Tag Archives: scripts

Generate script for objects along with data using Microsoft SQL Server Database Publishing Wizard

Last week, we had a request from the Application Team to generate the DDL script for all objects on a Production database. Since the number of objects on that database was quite high (more than 100,000), the Generate Script wizard in SSMS stopped responding. Later on we informed the Application Team to go for backup/restore the database, instead of waiting forever for the DDL script to be generated.

While I was looking out for better options to script out the database, I stumbled upon the Microsoft SQL Server Database Publishing Wizard tool by Microsoft. This is a good utility to generate scripts for exporting a database (SQL Server 2000 & 2005) to a file. This generates the T-SQL commands for the schema and data inside the tables. Here are some of the screenshots of the Microsoft SQL Server Database Publishing Wizard.

The script generated by this tool looks like the one below.

/****** Object:  Table [dbo].[TimeTable]    Script Date: 03/19/2011 12:38:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimeTable]') AND type in (N'U'))
DROP TABLE [dbo].[TimeTable]
/****** Object:  Table [dbo].[TimeTable]    Script Date: 03/19/2011 12:38:59 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimeTable]') AND type in (N'U'))
CREATE TABLE [dbo].[TimeTable](
	[StartTime] [datetime] NOT NULL,
	[stoptime] [datetime] NULL

INSERT [dbo].[TimeTable] ([StartTime], [stoptime], [downtime])
	(CAST(0x00009D1800E8F0C2 AS DateTime), CAST(0x00009D1800E8C4AF AS DateTime))
INSERT [dbo].[TimeTable] ([StartTime], [stoptime], [downtime])
	(CAST(0x00009D1800F3E452 AS DateTime), CAST(0x00009D1800F3B415 AS DateTime))

Since the data export progress is not very elaborate, while exporting huge databases one gets the feeling that the tool is hung. However, this utility does a good job while exporting data for relatively small databases and does it quickly.

Clean up Maintenance Plan History

Sometime back I had responded to a question where the user wanted to know the methods to cleanup the Maintenance Plan history. It is common for the DBA to come across a situation where the Maintenance Plan history grows large and as a result the Log Viewer takes a long time to load. Here are some of the approaches to clean up the Maintenance Plan history.

History Cleanup Task

SQL Server Maintenance Plan GUI has a task named History Cleanup Task. Using this task in the Maintenance Plan will help to clean up the Maintenance Plan History for the duration selected in it.

The History Cleanup Task has an option to selectively delete the history for Backup and Restore, SQL Server Agent Job and Maintenance Plan.

Log File Viewer

This is one of the lesser known options in SSMS. Using the Log File Viewer also the Maintenance Plan History can be cleaned up as shown in the below screenshot.

sp_maintplan_delete_log stored procedure

The Maintenance Plan History can also be cleaned up through T-SQL commands. The undocumented stored procedure sp_maintplan_delete_log can be used for this. The syntax for this stored procedure is as follows.

sp_maintplan_delete_log @plan_id, @subplan_id, @oldest_time


If I want to clear the history which is older than today for all the Maintenance Plans, this script would do the job for me.

EXECUTE msdb..sp_maintplan_delete_log null,null,'2010-03-16T00:00:00'

Script to check the Page File Settings

Last week when my team was working on a Performance issue, I wanted to check the Page File setting on that Cluster Node. Usually I navigate to the System Properties to check the Paging File settings.

Since that instance was critical one, the performance issue on it had got a lot of attention. Hence all the sessions on that instance were in use and I could not Remote Desktop to the node. At that time me and my colleague were discussing if there was a script to check the Page File allocation on a server. I did not have any scripts in my repository at that time. That night I came up with the below script.


On Error Resume Next
set oArgs=wscript.Arguments
Set objStdOut = WScript.StdOut
ServerName = trim(oArgs(0) )
Set objWMIService = GetObject("winmgmts:\\" & ServerName & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_PageFile")

For Each objItem in colItems
	objStdOut.WriteLine " "
	objStdOut.WriteLine "-----------"
	objStdOut.WriteLine "Name: " & objItem.Name
	objStdOut.WriteLine "Initial Size: " & objItem.InitialSize & " MB"
	objStdOut.WriteLine "Current Size: " & objItem.FileSize/(1024*1024) & " MB"
	objStdOut.WriteLine "Maximum Size: " & objItem.MaximumSize & " MB"
	objStdOut.WriteLine "-----------"


This script accepts the Server Name as the parameter. Hence this script can be used to check the Paging File settings on the servers across the network. If this script is saved as checkpf.vbs the command to check the Paging File would be


cscript checkpf.vbs ServerName


When I executed this script on a server which had the Page files on three different drives, the output was as below.

Find the Last Reboot time of the Server

Last week I had posted about some of the approaches I frequently use to find out when a SQL Server instance was restarted. Here are some of the methods I usually use to find out when a Server was rebooted.

Event Viewer

The Event Log service is the first one to start when a Server is rebooted. The Event ID 6005 is associated with the Event Log startup.


Uptime is a very useful utility to find out since when the Server is up. The other good thing about this tool is that the uptime of server across the network can be obtained from a single location. The command to get this information is uptime \servername.

This utility is not installed by default with Windows, it needs to be installed separately.


systeminfo provides detailed information about the computer configuration and hardware. Along with that it also provides the uptime of a given computer. Similar to uptime.exe this utility can also be used to get the information servers across the network. Since the output of this utility is very lengthy, the find parameter can be used to filter the requisite information. The command to find the uptime of the local server is as below.

systeminfo | find  "System Up Time"

Since this utility finds all the information related to hardware, at times it takes a lot of time to fetch the information. In my opinion, just to get the uptime of a server this utility is little too heavy.

Net Statistics

Net Statistics displays the time since the Server started collecting the statistics (which is also the time since the server was rebooted). The command to get that information is as follows.

net stats srv | find "Statistics since"

This is a light-weight tool which gives the information real quick.

Please share if you follow any other method to find out when a server was rebooted? Share your comments.

When was SQL Server restarted?

Over the weekend, I interacted with some of the “wanna be” DBAs. During the interaction I checked with them how they would find when was the SQL Server instance restarted? Everyone had a different approach to find out the information. Here are some of the ways to find out the date & time on which SQL Server service was last restarted.

SQL Server Error Log

By making use of sp_readerrorlog, first time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.

sp_readerrorlog 0,1,'Copyright (c)'

This approach is not useful if the SQL Server Error Log is cycled manually.

Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

But this query does not work in versions which are lower than SQL Server 2008.

Using sys.dm_exec_sessions DMV

The following query will get the Login time of the Session Id 1.

SELECT login_time FROM sys.dm_exec_sessions
WHERE session_id = 1;

session_id is created when the SQL Server is started and the login time remains the same until the service is restarted again.

Start time of the Default Trace

The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces
where is_default = 1

This approach is useful except for the instances where the Default Trace is explicitly disabled.

Creating date of tempdb

The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

The above query works on SQL Server 2005 and above. For SQL Server 2000 the same query will vary a little bit.

SELECT crdate FROM sysdatabases WHERE name='tempdb'

This is the query which I always use to find out when the SQL Server service was restarted.

Space used does not get changed after dropping a column

Sometime back I came across a question in one of the forums. The problem description is as follows.

  • The user had a huge table
  • Due to some reasons he decided to drop a couple of columns
  • Even after the dropping the columns, the space used by the table remained the same as earlier.

Whenever I come across a situation like this, the first question that comes to my mind is “Are those variable length columns?“. Variable length columns are the ones which have the data type as varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Whenever a column having these data types is dropped, the space utilized by this column is not automatically reclaimed. Here is an example. I create a table which has a column with varchar data type.

CREATE TABLE [dbo].[TestTable](
	[ID] [int] NULL,
	[Name] [varchar](100) NULL,
	[Address] [varchar](1000) NULL

After inserting few rows into the table, I drop the column Address. Before and after dropping the table, I check the Average Page Space used in Percentage value.

SELECT page_count,avg_page_space_used_in_percent,record_count
FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed')
SELECT page_count,avg_page_space_used_in_percent,record_count
FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed')

After executing the query, the output looks like the one below.

As evident from the output, the page space used is the same. The space used by the Address column is not released.

In this situation DBCC CLEANTABLE command comes to the rescue. Executing this command after dropping a variable length column will release the space used by that column. Hence I include the following line after the DROP TABLE command.

DBCC CLEANTABLE ('dbname', 'TestTable', 0)

After executing the same query with this extra line, the output looks like this.

DBCC CLEANTABLE had done the trick! This command comes in handy especially in Non-Production instances, where drive space is a constraint and adding/dropping columns is a usual practice.

Unable to determine if the owner of job has server access

On Wednesday, I was working with my team member to fix some of the jobs failing on a SQL Server 2005 instance. All these jobs were failing with the following error.

Unable to determine if the owner (domainlogin) of job User Databases.Backup - User Databases has server access
(reason: Could not obtain information about Windows NT group/user 'domainlogin', error code 0x534. [SQLSTATE 42000] (Error 15404))

These jobs were executing few Backup and Rebuild Index Maintenance Plans. As the error message stated, the Job Owner did not have access on the instance. The user had access when the Maintenance Plans were created but not anymore.

If the jobs are failing because the job owner does not access, the usual approach is to change the Job Owner to a login which has requisite privileges on the SQL Server Instance. But in this case it would not help. The job owner can be changed. Since these jobs are created by the Maintenance Plans, any modifications done to the jobs would be wiped out if the Maintenance Plan is modified. Just opening the Maintenance Plan and clicking on the Save button is enough to undo all the changes done to the corresponding SQL Agent jobs.

Next step? Modify the Owner of the Maintenance Plans. But SSMS does not have an option to modify the Maintenance Plan owner through GUI. Like Andrew Calvett mentioned in this post, modifying the ownersid column in sysdtspackages90 changes the owner of a Maintenance Plan.

Executing the below query would list the current owner for all the Maintenance Plans in an instance.

SELECT name as PackageName, suser_sname(ownersid) as Owner
FROM msdb..sysdtspackages90

The output would be like the one below.

We decided to change the Maintenance Plan ownership to sa login. Executing this script would do it for us.

UPDATE msdb..sysdtspackages90
ownersid = SUSER_SID('sa')
WHERE name = 'MaintenancePlanName'