In this tutorial, you’ll see the complete steps to create a table in SQL Server Management Studio. You’ll also learn how to create a table using only queries. Steps to Create a Table in SQL Server Management Studio Step 1: Create a database. If you haven’t already done so, create a database in SQL Server Management Studio. I have downloaded the SQL Server Management Studio (SSMS) 2016 to recover a huge.bak file which is an old backup of a database. But first of all I need to connect to a DB Server. After install, I execute SSMS and it asks me to connect to a DB Server.
Problem
When you install SQL Server, it has a few default configuration settings for SQL Server to manage logs. In some instances, where servers generate too much log info and you need to look at the log it takes a long time to pull up. Also there maybe cases where you want to keep more log info for some jobs versus others. For example, I have a job that runs every minute and it generates a lot of log history, but I only care about the data for the last 3 days. For another job that runs daily, I want to keep the log info for 15 days. I started with a process to pull all history and store in a separate user table, which is still very useful, but for day to day operations I was looking for an easier way to manage this for all of my SQL Server 2005 and later servers.
Solution
There are various logs that exist on SQL Server. For this tip I am going to cover the below topics and briefly talk about the syspolicy_purge_history job on SQL 2008.
SQL Server Logs
SQL Job History
Purging SQL Server Job History
Jeremy Kadlec wrote a article Retaining SQL Server Job History and it explains how job history is stored which you may want to read first. What I am going to do is to customize the options and control the SQL Job history. Note that this only works for SQL Server 2005 and later.
First, you will need to disable all the options as shown below. You can get to this screen by right clicking on SQL Server Agent and select Properties.
Or if you prefer, you can use T-SQL script below to change these properties shown in the screenshot above.
Now, create a new SQL job to run once a day and put in this T-SQL code. In this example I will only keep the job history for the last 15 days.
Now, assume I have a job called 'Pay Roll Over' and it runs every 10 minutes and I only need to keep the last 3 days of the job log for troubleshooting purposes. To make this customization you just need to add another step to a job as follows.
There you go. You now have a job to keep only the last 15 days of the job log history and only the last 3 days for job 'Pay Roll Over'. If you need to do this for other jobs, you can just add additional steps.
I have a similar situation with the SQL Server Logs where the logs get really large and it takes along time to open the log to find errors. To handle this, you can use sp_cycle_errorlog to close the active error log and create a new error log. This works for older versions of SQL server like SQL 2000 as well.
You can create a new job to run each day or on whatever schedule you prefer. The job step should run the following command.
One thing to note is that the default setting for SQL Server only keeps the last 7 logs (active plus 6 archives) as shown below.
This may be OK for some to only keep the last 7 logs, but for most cases that may not be enough. If you need to keep more than 7 logs, you can read this article 'How to increase the number of SQL Server error logs'.
This can also be done by updating the registry which is usually what I do by running the below script to apply the change. This will increase the value to 10 archive logs.
This is how it looks if you use RegEdit.
and it will let you keep 10 archive copies as shown below.
Policy Purge History job (syspolicy_purge_history)
The job syspolicy_purge_history is part of the default installation and you want to make sure this job doesn't fail. It is a known issue that for clusters this job may fail and I just wanted to point this out. You can read this article 'Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance' for more information.
The issue is caused by the name of the server. Navicat premium 12 1 20 download free. Assume your physical SQL Server name is 'MYSERVERNode1' and your Virtual SQL Server Instance name is 'MYSQLSERVERSQL2008A':
This is the before value in this job that causes the issue
and this is what it should be
This change will address the issue without recreating the job, at least this is the case for all the clusters that I have worked with.
Sample Code to Create a Job
Here is the code that I used to create the job for this sample. You can customize as needed. You should remove the 2nd step from the script as it is customized to purge a specific job that I mentioned above in the example.
Next Steps
If you have general scripts that you run after SQL Server installation, this is another good script to run after installation is done to keep your servers consistent.
Last Updated: 2011-01-14
About the author
Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development. View all my tips
Related Resources
By: Greg Robidoux | Updated: 2018-06-02 | Comments (7) | Related: More >SQL Server Agent
Sqlpro Studio 1 0 325 – Powerful Database Manager Jobs
Problem
When working with SQL Agent jobs sometimes it is difficult to determine why ajob failed or even exactly what occurred. For each job step SQL Server providesa message, but it is not always that easy to determine exactly what was occurringduring that step. The default message that is saved in the job history is1024 characters. In most cases this may be enough, but if you have a longrunning process you may need to store more than 1024 characters. Is there any wayto get additional information within the job history to help troubleshoot issuesas well as just knowing what occurred?
Solution
In SQL Server 2005 and later you have the ability to log additional SQL Agentjob output beyond the 1024 characters that is stored in the msdb.dbo.sysjobhistorytable. By default this enhanced logging is not turned on it is something thatyou need to turn on for each job step.
Let's take for example we are running DBCC CHECKDB commands for several databases. This command provides a lot of output data unless you use the WITH NO_INFOMSGS option. If you are not using the NO_INFOMSGS option the command output fills up the 1024characters quite quickly and you can only see the part of the output in the jobhistory message.
Here is a screen shot of the job history for the step that did a DBCC CHECKDB. As you can see we only get a portion of the command output.
To allow additional data to be logged you need to turn on some settings for eachjob step in your job. To do this edit the job step and select the Advancedtab.
In order to see the additional logged information you need to use this storedprocedure sp_help_jobsteplog or you could query the msdb.dbo.sysjobstepslogtable directly.
If we run this command in a query window (test2 is the job name):
we will get this additional output: (note: the output was edited to removemost of the middle to keep this web page smaller)Wifi scanner 2 7 9 download free.
Job 'test2' : Step 1, 'DBCC' : Began Executing 2007-12-13 21:06:14 DBCC resultsfor 'Test1'. [SQLSTATE 01000] Service Broker Msg 9675, State 1: Message Typesanalyzed: 14. [SQLSTATE 01000] Service Broker Msg 9676, State 1: Service Contractsanalyzed: 6. [SQLSTATE 01000] Service Broker Msg 9667, State 1: Services analyzed:3. [SQLSTATE 01000] Service Broker Msg 9668, State 1: Service Queues analyzed:3. [SQLSTATE 01000] Service Broker Msg 9669, State 1: Conversation Endpointsanalyzed: 0. [SQLSTATE 01000] Service Broker Msg 9674, State 1: ConversationGroups analyzed: 0. [SQLSTATE 01000] Service Broker Msg 9670, State 1: RemoteService .. .. .. sys.queue_messages_2041058307'. [SQLSTATE 01000]There are 0 rows in 0 pages for object 'sys.queue_messages_2041058307'. [SQLSTATE01000] DBCC results for 'Table1'. [SQLSTATE 01000] There are 62 rows in 1 pagesfor object 'Table1'. [SQLSTATE 01000] DBCC results for 'sysarticles'. [SQLSTATE01000] There are 2 rows in 1 pages for object 'sysarticles'. [SQLSTATE 01000]DBCC results for 'sysarticlecolumns'. [SQLSTATE 01000] There are 4 rows in 1pages for object 'sysarticlecolumns'. [SQLSTATE 01000] CHECKDB found 0 allocationerrors and 0 consistency errors in database 'Test1'. [SQLSTATE 01000] DBCC executioncompleted. If DBCC printed error messages, contact your system administrator.[SQLSTATE 01000]
As you can see we can now get the entire output message since the output in thesysjobstepslog is stored as a nvarchar(max) instead of an nvarchar(1024) like insysjobhistory.
Next Steps
Helpfully this tip is useful for you to get additional job step history,just a couple of clicks can provide a lot more detail
Keep in mind that only one row is stored per job step. If you usethe 'Append output to existing entry in table' this will just append the datato the existing record.
Take a look at these other usefulSQLAgent tips
Last Updated: 2018-06-02
About the author
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. View all my tips