Newsflash

Most of the SQL Developers/DBA would have come across a situation where they need to store the temporary result sets. This is where Temp tables and Table variables come into effect and helps in storing the data sets in a temporary location.

Recent Objects - Without Triggers

Print
PDF

Written by Deepak | 05 May 2010

I was thinking about writing a script to capture the objects that were either CREATED/DELETED or MODIFIED in the last 24hrs. Instead of directly querying the sys.objects I from where I can only get minimal information I decided to get a whole bunch of useful information using the SQL Server default trace.

By default the default trace will be running in all the SQL Servers since SQL 2005 onwards. It will capture minimal information without overloading the database. It will be placed in the same location as that of the SQL Server errorlog. The maximum file size is 20MB after which the trace will roll over into a new file. However, SQL Server can maintain only the 5 most recently created trace files. In addition a new trace file is generated whenever SQL Server is restarted.

Attach all user databases

Print
PDF

Written by Deepak | 04 May 2010

I was doing an upgrade of one of our development servers to SQL Server 2008. To minimize the downtime I decided to attach all the user databases using script. Basically, I am attaching the db from the same location there were present earlier to being detached.
Consider the following example, If I have a database named "Test" in SQL 2005 that is currently residing in the following locations, C:\database\Test.mdf & C:\database\Test_log.ldf and another database named "Test_2008" in C:\temp\test_2k8.mdf & C:\temp\test2k8_log.ldf then the script I wrote would prepare the script to attach the database test from the same location.

Read more... Add new comment

Capture SQL Performance Counters through TSQL

Print
PDF

Written by Vidhya Sagar | 09 April 2010

Performance counters are useful to gather information about server status and we can analyze the server status later using these data. In my environment one of my client is not ready to pay or buy metrics tools (normally we will be using BMC, HP etc). Our windows team has written they own custom scripts to capture the counters related to them, then it comes to our team how we are going to capture these data. Immediately it strikes my mind that we have a DMV “sys.dm_os_performance_counters” to get SQL performance counter values. So I thought of utilizing this procedure to get SQL Performance counter values and storing it in a table which will help us in the future to analyze the data.

Read more... Comments (2)

Index fragmentation Report

Print
PDF

Written by Deepak | 01 March 2010

I had to prepare the list of tables having more than 60% fragmentation. I wrote this query to get those information. The below query will give the list of all the fragmented tables in a particular database having more than 60% fragmentation. This will work for SQL 2005 and beyond.

Read more... Comments (2)

Function to Split Comma separated string to Integer

Print
PDF
(1 vote, average 5.00 out of 5)

Written by Deepak | 05 February 2010

 

There are cases where we need to pass the input parameter to the stored procedure as a comma-separated string in order to avoid multiple db calls from happening. This helps in reducing the db calls thereby reducing the load on the db server.

The below function will split the given comma-separated string into integers and process the results.

Read more... Comments (3)

More Articles...