SQL Server – Track Database Size Growth Trend

One of my DBA friends asked me to if we can track the size growth of a production database happened on daily basis. However looking into the system tables, there is no option to get database growth over a period of time. So to achieve this report we need to have the database size information collected daily or what ever time frame we need the growth report. In this port we will go step by step for this task.

First create a table to keep the size information of each database with a date on which the information is collected:

CREATE TABLE DatabaseFileSize
(
[database_id] INT,
[file_id] INT,
[file_type_desc] NVARCHAR(120),
[name] NVARCHAR(128),
[physical_name] NVARCHAR(520),
[state_desc] NVARCHAR(120),
[size] INT,
[max_size] INT,
[growth] INT,
[is_sparse] BIT,
[is_percent_growth] BIT,
[collect_date] DATETIME
)
GO

Create a stored procedure to populate this table with the current database size information:

CREATE PROC usp_util_CollectDatabaseSize
AS
BEGIN
SET NOCOUNT ON
INSERT INTO DatabaseFileSize
(
[database_id],
[file_id],
[file_type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
[collect_date]
)
SELECT
[database_id],
[file_id],
[type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
GETDATE()
FROM sys.master_files
SET NOCOUNT OFF
END
GO

Now we need to populate the table over a period of time, so we need to create a SQL Agent job to execute this stored procedure and schedule this job to run everyday.

USE [msdb]
GO
/****** Object:  Job [Collect_Database_Size_Info]    Script Date: 02/13/2013 23:47:43 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 02/13/2013 23:47:43 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect_Database_Size_Info',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'Domain\User', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DBSize]    Script Date: 02/13/2013 23:47:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBSize',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC usp_util_CollectDatabaseSize',
@database_name=N'AuditDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DailyDBSize',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130213,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'445fb175-517b-4221-b6b8-6c6877dc7c24'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Now we have the information over the period of time and we can run our below query to make a report out of this data.

Track the whole database size growth trend over a period of time:

--DB Size Growth Trend
SELECT
DB_NAME(database_id) AS DatabaseName,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS DBSizeInMB
FROM DatabaseFileSize
GROUP BY database_id,collect_date
ORDER BY DatabaseName,collect_date

Track the log file and data file(s) size growth trend over a period of time:

--DB File Size Growth Trend
SELECT DB_NAME(database_id) AS DatabaseName,
(CASE file_type_desc
WHEN 'ROWS' THEN 'Data'
WHEN 'LOG' THEN 'Log'
END) AS FileType,
physical_name AS PhysicalPath,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS FileSizeInMB
FROM DatabaseFileSize
GROUP BY database_id,physical_name,file_type_desc,collect_date
ORDER BY DatabaseName,collect_date

SQL Server – Search Text in Stored Procedure

Here is the query to search text in stored procedure definition:

DECLARE @SearchText NVARCHAR(500)
 SET @SearchText = 'Hierarchy'
SELECT DISTINCT
 o.name AS [Object Name],
 o.type_desc AS [Object Type],
 m.definition AS [Definition],
 m.uses_quoted_identifier
FROM sys.sql_modules m
INNER JOIN sys.objects
 o ON m.object_id=o.object_id
WHERE m.definition LIKE '%' + @SearchText + '%'
 AND o.name LIKE 'usp_%'

SSIS – Error: Could not get a list of SSIS packages from the project

While working on a ssis project recently I was trying to build the solution however it was failing again and again without showing any error in Error List !!! and when I checked the Output List it was saying the below message (refer Fig-1).

Error: Could not get a list of SSIS packages from the project.
===== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped =====

Fig-1

This message says there is some missing objects in the project (.dtproj), as CreateDeploymentUtility property was set to True for this project the ssis was not able to show the actual error message.

So set the CreateDeploymentUtility to False and build the project again to get the actual error. In my case there was a package added in the project however because of some source control issues the .dtsx file was not available in the project directory.

Now that you got the error to fix; go ahead and fix the issue then set CreateDeploymentUtility property to True and build the project successfully.

SSIS – Create New Excel File Dynamically to Export Data

Recently I got a requirement to export error data to a newly created excel file every time the SSIS package runs. The challenge here was to create new excel file with time stamp every time the package ran and export data to that newly created file.

In this post will demonstrate this step by step.

• Create a template file to export the data. You need this template file to set up the package for the first time. Once the setup is done you can delete the template.

• Create connection managers:
  • Add Excel Connection Manager pointing to the template excel file created before.
  • Add OLEDB Connection Manager pointing to the desired database (here I am using AdventureWorks2008R2 database)

• Add an Execute SQL Task and use the below script as SQL Statement as shown in fig-2:

CREATE TABLE Data (`DepartmentID` LongText, `Name` LongText, `GroupName` LongText)

Fig-1

Fig-2

• Now setup a Data Flow Task to export the data.
  • Add a Data Flow Task to the package. Open the Data Flow Task, inside Data Flow tab add a OLEDB Source, select SQL Command as data access mode and use your query to fetch the required data. I will use the below query:

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department

• Add an Excel Destination to the package. In the excel destination editor select Table or View as Data Access Mode, select the name of excel sheet and do the required mapping. Set ValidateExternalMetadata property to False.

Fig-3

• Now go to Expressions property of Excel Connection Manager and set ExcelFilePath with below expression:

"S:\\ETL Lab\\CreateNewExcel\\ExportData_"+REPLACE((DT_STR, 20, 1252)(DT_DBTIMESTAMP)@[System::StartTime], ":", "")+".xls"

and the package is ready to run.

If you need the sample package contact me.

CHECK constraint with User Defined Function in SQL Server

Reblogged from SQL Journey:

This post describes how to use result of an user defined function with CHECK constraint in SQL Server.  For demonstration, considered a situation where it is not  allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.

So for this first we need to create the function before creating the CHECK constraint.

Read more… 321 more words

SQL Server – Resolve Collation Conflict with Temp Table

When one of our projects deployed to production application was throwing a strange error and while debugging it is found that one of the stored procedure getting failed because of collation conflict issue.

The reason behind this error is the stored procedure used a temp table to join a table on string column. Temp tables are created on TempDB, so if you don’t specify collation for a string columns they inherit the collation of TempDB. If the collation of user database doesn’t match with the collation of TempDB and you are comparing and/or joining on string columns, it may cause this collation conflict issue.

Resolution:

There are many ways to resolve this issue. However the best way as per me is to change to specify the collation of the string column of temp table to database default, so that it will inherit the collation of .user database.

CREATE TABLE #tmpTable(IDCol INT, StringCol VARCHAR(100) COLLATE DATABASE_DEFAULT)

Smart coding

SQL Server – List of Tables Used in Stored Procedure

Today while developing a piece of logic in our development, my colleague said that I know there is a procedure which has same kind of logic and that may help us building the logic quickly. But the question was how to find that procedure !!!

Then the idea came up was if we can find the list of procedures that are dependable on Table1 & Table2 ?

…and that inspired me to write this post.

We spend some time researching system tables and here is the findings to find list of tables used in a procedure.

;WITH procs
AS
(
SELECT o1.name AS proc_name,
o2.name AS table_name,
ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o1
ON o1.id=d.id
INNER JOIN sysobjects o2
ON o2.id=d.depid
WHERE o1.xtype = 'P'
--AND o2.name = 'tabname1' OR o2.name = 'tblname2'
)
SELECT proc_name, table_name
FROM procs
WHERE row = 1
ORDER BY proc_name, table_name

Let me know if you have any better idea for this.

SQL Server: Allow Only Alpha Numeric Characters to a Column

Here is how to restrict non alpha numeric characters to a column in SQL Server

--Create Demo Table
CREATE TABLE AlphaNumDemo ( DemoCode NVARCHAR(100) )
GO
--Restrict Special Characters
ALTER TABLE AlphaNumDemo ADD CONSTRAINT chk_AllowAplhaNumericCharactresOnly
CHECK (DemoCode NOT LIKE'%[^a-zA-Z0-9 ]%')
GO
--Insert Test Data
INSERT INTO AlphaNumDemo
 VALUES ('demo1')
INSERT INTO AlphaNumDemo
 VALUES ('Demo1')
INSERT INTO AlphaNumDemo
 VALUES ('Demo 1')
INSERT INTO AlphaNumDemo
 VALUES ('Demo-1')
INSERT INTO AlphaNumDemo
 VALUES ('#1Demo')
GO
--Check Data
SELECT
 DemoCode
FROM AlphaNumDemo
GO

--Cleanup
DROP TABLE AlphaNumDemo
GO

In the above demonstration observe the records with special characters will not be inserted to the table.

SQL Server: Kill process running xp_cmdshell

In our last post we discussed how to run a VB Script from SQL Server. Here I will share something interesting related to that task. Well, while extending the program logic of my VB Script I have used some message boxes to check the flow of some variable values. I also ran that script from command line, that finished successfully.

Now, when I try it to run my TSQL code (which uses xp_cmdshell to run that script) and observed that it is taking an unexpected time to complete !!!  After sometime I think of killing the process and killed, but still the query was running where I got a Success message from KILL !!!

Here is the reason behind that…after testing my script I forgot to remove/comment the message boxes, which cause my TSQL to run for an indefinite time.

Solution:

• The script or application we are calling from TSQL (xp_cmdshell) should be self-sufficient, i.e. application should not have any user interaction like InputBox or MessageBox, Visual Windows…etc.
• To stop the query which call such applications, we need to end the process from windows Task Manager.

Note: xp_cmdshell always run the target application in background and user can’t end it interactively to regain the control from that application.

More information for this topic is available here on Microsoft Support site or refer Book Online. Hope this helps you.

lated to

SQL Server: Running VB Script From TSQL

In this post we will see if we can run a VB Script from SQL Server.  Yes, SQL Server allows to run external Scripts. So, here is the process how to run a VB Script from TSQL Code.

Well to test this lets first create a sample VB Script which will just display a message.

Dim VarSetMessage
VarSetMessage = "Running VB Script from SQL Server..."

'Display message
WScript.Echo VarSetMessage

Save it as “myMsg.vbs” on “C:\” drive of your machine. Okey, before running this script from SQL Server, lets see how to run it from Command Prompt. To do this:

Start => Run => cmd => ENTER
Type Cscript C:\myMsg.vbs => ENTER

(Refer below image for result)

Now to run the VB Script from SQL Server just run the same command line we tested above using xp_cmdshell from SQL Server.

EXEC XP_CMDSHELL 'CScript C:\myMsg.vbs'

and we are done !!!

Note: You may need to configure xp_cmdshell if not configured before.