SQL DBA Blog

Category: SQL Server

SQL Server Blog Archives, by Peter Whyte (Database Administrator).
Includes: Database Admin, Management Studio (SSMS), Windows Admin, PowerShell, WSL, MSSQL Backups, Restores & more…

Exporting SQL Server Query Results to CSV

This post contains a guide on how to export data from a SQL Server database using SSMS.

SSMS, SQL Server Management Studio, is the standard application that most people use to run queries for a Microsoft SQL Server Database. The default export option in SSMS is set as Column-Aligned, and we need to change it to Comma-Delimited to get our query output formatted correctly for CSV.

To export a SQL query to CSV in SSMS we use the Results to File option which is shown in the demo below in this post. We use this instead of the standard query output option, Results to Table.

The first part of this post runs through how to export a SQL query to a file using the default output options in SSMS, and then we are changing the output format column options as mentioned above.

# Export SQL Server Query to File (Default SSMS Options)
# Change SSMS Output Format Options (for CSV Files)

Export SQL Server Query to File

To export a query the simplest way, would be for us to click Results to File as marked below.

When you run the query, it’ll prompt asking where and what to save the file as.

The default file type is as shown above, a .rpt file. If we open that in NotePad++, it’s looking like this:

We’ll save it as CSV this time.

You will now likely want to change the delimiter after looking at this output file. This .rpt file should not be opened as CSV.

Change SSMS Output Format Options

Now that we’ve looked at the default SSMS export file option, let’s change it to Comma Delimited so we can open SQL query outputs as CSV.

Within SSMS, go to Tools > Options as shown in the screenshot below.

In the Options window that appears, navigate to Query Results > SQL Server > Results to Text

The default option is shown above, Column aligned. Change this to Comma delimited.

We now need to disconnect our current session within the SSMS Object Explorer and connect back to the SQL Server instance.

Now, if we run that query as we did above outputting as text, the output file should be formatted correctly when saved as CSV.

You may have identified an issue with the exported CSV data, and it contains “rows affected” at the end of it. The reason for this is that we need to include SET NOCOUNT within the SQL query when using Results to Text. I talk more about this in another one of my posts – Count Rows in CSV Files > Checking for Data Issues

I hope this guide has helped you export a SQL query to CSV today. If you would like more SSMS-related tips, feel free to have a look at my SSMS page which is frequently updated.

October 2, 2018
How to Check Table Sizes in SQL Server
There are many ways to check the size of a table in SQL Server. The way you will do it will likely depend on what your task at hand is.

For example, a quick look at the Disk Usage by Top Tables report on a database you’ve never seen before would instantly show you the large tables in a database. But if you want this information saved somewhere else, e.g. to a table in SQL, it’s an easy task for us to run a TSQL script and output to Excel or CSV.

In this post, I’m sharing a variety of methods to check table sizes in MS SQL, including:
# SSMS Disk Usage by Top Tables Report
# Check Table Properties in SSMS
# MSSQL sp_spaceused System Stored Proc
# MSSQL Script to Get Table Sizes

SSMS Disk Usage by Top Tables Report

To open this report:-
1. Open SQL Server Management Studio (SSMS).
2. Expand Databases.
3. Right-click the Database and select Reports > Standard Reports > Disk Usage by Top Tables

The report shows that this database has 2 tables close to 700MB in size, both containing the same number of rows but there are some differences in the indexes.

I find this method one of the most efficient methods for getting the disk usage of tables in MSSQL.

Check Table Properties in SSMS

Another way to check the sizes of tables in SQL Server Management Studio (SSMS) is to open the Table Properties within the Object Explorer.

To do this, expand the database and tables. You may need to add a filter to find your table.

Now, right-click the table and select Properties as shown in the screenshot below.

Open the Storage tab on the left-hand sidebar. It may take a second for the window to load, but this will show the index space, data space usage and the table row count.

MSSQL sp_spaceused System Stored Proc

For this and the following methods to get SQL table sizes, we need to open a new query window.

We can run the sp_spaceused MS SQL system stored procedure, adding the Schema and Table names as parameters.
```
-- Check Table Sizes (MS SQL System SP)
sp_spaceused '[dbo].[FactResellerSalesXL_CCI]';
```
MSSQL Script to Get Table Sizes

Lastly, we can run a query on SQL Server system tables to get the table sizes.
```
-- Get Table Size SQL Server Script
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name
```
That’s us done for now. For more SQL Tips from a random MS SQL DBA in the field, feel free to check out my SQL DBA Blog page which includes a list of my latest posts.
October 1, 2018
Backing Up a SQL Server Database
Backing up a database is something I’ve always found easier done within a query window. There’s not much to it;
```
BACKUP DATABASE [DATABASENAME] 
	TO DISK = N'e:\directory\databasename_backupdate.bak' 
		WITH COMPRESSION, STATS
```
That’s what I’m used to writing. I’ve no need to worry about the copy_only option these days, or check SQL Server Editions to see if I can use compression. Good times that was. Measuring available disk space against estimated backup sizes, and sometimes even adding temporary virtual disks to the server to accommodate the backup file size. The SQL statement for backups is simple… but there’s a lot to consider when performing a one. It just depends on the restrictions of the servers you are working with really.

Below is a simple run-through for backing up a database using the Back Up Database Wizard. As always, have a look at the manual first.

How to Backup a SQL Server Database

1. Right click your database, select Tasks and click Back Up…

2. In the General tab of the prompted window you’ll see it’s already set-up for a Full Backup on AdventureWorks which is using the Simple Recovery model.

3. I’ll remove the destination file and move it somewhere else.

The directory has been picked up by my chosen Database Settings. Type the new backup file name here, remembering the .bak at the end.

4. Over to the Media Options tab, there’s a few options for consideration. Such as verifying the backup or performing checksums. You might think that verifying backups with automatic offsite restores works for you. Or, maybe you’re happy relying on the VM backups? As the importance of your database backups grows, the deeper it goes. I’m leaving those options unchecked as I don’t need them, and all other options are set as default.

5. In the Backup Options tab, I’m leaving all options set to their defaults. No backup expiry or encryption, and I’m going to trust I’m using compression as default on my SQL Server.

6. Click the Script button at the top of the window to see the generated SQL code behind this backup operation.

7. Some of the code contained within the statement is unnecessary, but it’s doing the same thing anyway. We can run this command or click OK on the Wizard.

8. And when you’re done…

You have a backup of your database!
September 16, 2018
Restoring a SQL Server Database

This is a simple guide to restoring a full backup file in SQL Server, providing an overview of a few options that are available during the process.

It is always a good idea to read through Microsoft’s documentation on database restores before proceeding. This will ensure that you have the most up-to-date and accurate information.

How to Restore a SQL Server Database

1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Right-click on the “Databases” folder and select “Restore Database”.

3. In the next window, select “Device” and then click on the three dots (…) to add a backup file.

4. Navigate to your database backup file and click “OK”.

5. You can change the name of the database in the “Database” field. Once you have done that, click on the “Files” tab on the left-hand menu.

6. On the “Files” tab, you can define the locations for your data (.mdf) and log (.ldf) files. You can also use the “Relocate all files to folder” function to quickly relocate multiple files.

7. On the “Options” tab, you can choose the recovery state of your database. If you don’t have any other backups to restore, you can select “WITH RECOVERY” to make the database ready for use as soon as the restore is complete.

9. Once you have configured the options for your restore, you can generate a TSQL script by clicking on the “Script” button at the top of the window.

>>

10. Instead of clicking “OK” on the wizard window, hit “F5” to run the script and restore the database.

11. Once the restore is complete, refresh the database view in Object Explorer to see the restored Adventureworks database.

September 4, 2018
Batch Deletions in SQL Server

This script will delete rows within a table that are out-with the specified retention period. This can be useful if you’re running deletes on large tables, and particularly if;

# Deletes are taking a long time & you’re finding it difficult to track progress.
# You have a short maintenance window each day to perform such tasks, which could be due to caution over blocking/deadlocks.
# Your server is not equipped to handle the log file bloat associated with running the delete as a single transaction.

Whatever the reason is, this is a useful script to have in the toolbelt.

I’ve set this to delete 10k rows at a time, although you can find your own sweet spot.

To find out how long it’ll take until all rows have been deleted, you could;
1. Get row counts.
2. Run deletion script for 10 minutes.
3. Check how many rows were affected.
4. Refresh Disk Usage by Top Tables report / get new counts.
5. Compare numbers above.
6. If the math works out to be 9 months before it’ll finish, then get an index added.

August 30, 2018
Copying Dates into Excel

There’s often a need to quickly copy/paste a database query output into an Excel sheet, rather than exporting as a .csv file. When you do this for a DATETIME column though, it’ll show up in Excel looking something like this.

I don’t think Excel likes the milliseconds.

This is the data I tried to copy over.

To resolve, just convert the DATETIME columns to SMALLDATETIME, then go ahead with the copy/paste to Excel using the default Cell Format.

I’d be interested to know if there’s an easier way to sort this on the Excel side. For now, the above will do the trick for me!

July 9, 2018
Stack Exchange Data Explorer
As a DBA, I’ve found Stack Exchange Data Explorer to be a really useful tool for exploring and analyzing the data from the Stack Exchange network of websites. It’s kind of like having a SQL playground where you can run all sorts of queries and see what insights you can uncover from the data.

One of the things I like about Stack Exchange Data Explorer is that it’s web-based, so you don’t have to download and process the data yourself. You just fire up the tool, write your query, and see the results. It’s super easy to use and it’s a lot of fun to see what insights you can uncover from the data.

I’ve also found that Stack Exchange Data Explorer has a lot of pre-built queries and visualizations that you can use as a starting point for your own analysis. These queries and visualizations cover a wide range of topics, such as user activity, Q&A statistics, and tag popularity. It’s really interesting to see what other people have discovered from the data and it can give you some ideas for your own analysis.

Another cool thing about Stack Exchange Data Explorer is that you can share your queries and visualizations with others. This makes it easy to collaborate and share your insights with the broader Stack Exchange community. It’s a great way to learn from other people’s work and to share your own findings with others.

This is how it looks:

The interesting content is down at number 24 (Database Administrator site) in the list 😉

You can view other folk’s queries on each database, as well as create a query of your own as mentioned above. This can happen even by accident. If you run a query it creates a fork, which I didn’t know.

The top query above is my one.

My last example shows the SQL of a report I find interesting:
```
-- stack exchange data explorer, show top posts since last year
SELECT TOP 10 Title, ViewCount, Score, Tags, CommentCount
FROM Posts
WHERE CreationDate > DATEADD(year, -1, GETDATE())
ORDER BY ViewCount DESC
```
The interface is smooth and responsive. A perfect place to see what’s hot and perhaps to gain some xp in querying.

If you’re a DBA or just someone who likes to play with data, I highly recommend checking this site out.
July 8, 2018
More on SPIDs… and Killing Them

While looking at an old script today, one that kills SPIDs on a SQL Server database… I had an instant urge to try kill a system SPIDs, just to see what would happen. And the result was…

That’s a good thing though right? One less thing to be worrying about. You can’t kill system SPIDs.

There are many other facts and warnings in the Kill command’s documentation page, and here’s a few other tests I’ve done;

# You’re unable to kill your own SPID.
# You can check the progress of a killed SPIDs rollback.
# Microsoft’s warning when killing SPIDs.

You’re unable to kill your own SPID

You’ll see your SPID at the top of the query window next to the pin icon, or you can use @@SPID.

You can check the progress of a killed SPIDs rollback

This won’t work for all the SPIDs you kill. For example, it doesn’t work for a 20 minute running delete statement has been killed and you’re sitting waiting for it to come back to life. It’s probably whatever sys.dm_exec_requests can pick up on, but don’t take my word for anything.

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

Hesitate before the kill?

On the Microsoft documentation linked above it states that you should not kill processes with the following wait types:

AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SIGNAL HANDLER

I’ve yet to encounter issues as a result of a bad kill – when the occasional kill of a blocked or optimistic query does come along, it’s usually all good a few moments after the kill. Sometimes, perhaps there will be a calm 1 hour wait before a shrink db gets run too, who knows!

July 1, 2018
SQL Server Support

What’s the plan when you don’t know how to fix a production issue? You can prepare this situation by reading and keeping up to date. But when you’re sweating behind a keyboard, you haven’t moved out your chair for 4 hours and you’re being chased for an update, what do you do?… I thought I’d list a few thoughts on this.

Articles & Blogs

If you’re left searching the web for a solution during a change window, there’s a good chance the best call for action is to proceed with your back-out plan. If it’s an unplanned outage, things might be different.

The main consideration here is that you need to be able to trust that what you’re reading is technically accurate, but you’ll never truly know how something is going to work until you have tested it yourself. There’s always going to be hidden gotya’s!

See my other page, SQL Server Links & Tools, which includes a big list of amazing contributors within the data platform community.

Microsoft SQL Server Documentation

Microsoft’s official documentation, AKA the user manual:
https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation/
I think I spotted a troll on this pages comments. Win2000 on a year 2018 comment? (╯°□°）╯︵ ┻━┻
And lastly, here’s a link to Microsoft’s own blogging space.
http://blogs.msdn.com

Microsoft Customer Support Services

You will have to pay for support on a case-by-case basis, unless the problem is due to a bug in the product. Support cases are prioritised like in any IT environment, so if a production server is down the case will likely be marked as Priority 1 by the agent.

You may be asked to send SQLDIAG or PSSDIAG data. These are tools used to collect and analyse data to identify performance root causes. I had no idea what this was until I saw it mentioned in this book by Jonathan Kehayias and Ted Krueger.

Troubleshooting SQL Server, A Guide for the Accidental DBA

This is a free book given out on Red-Gate’s website. It’s crazy how decent the SQL Server community is. There’s also an Amazon link if you prefer a hard copy. It doesn’t dive into SQLDIAG/PSSDIAG’s but if you’re reading this and want proper information, have a look!

Hire a Consultant

Perhaps you need someone in to fill a technical gap for an on-going project, or just need a server optimised so you can sleep at night. Usually this is a decision based on budgets and a big consideration is who are you going to hire?

June 18, 2018
Logging sp_whoisactive to a Table

This is a follow-on post of sp_who, sp_who2 & sp_whoisactive and is a run-through of logging SQL Server activity to a table, using the sp_whoisactive stored procedure.

Even if you are equipped with a SQL Server monitoring tool, you still may be having trouble finding that slow/blocking query that runs during the night. Running sp_whoisactive periodically (e.g. every 30/60 seconds) and logging the results to a table will help you here.

As mentioned in the previous blog, there’s many parameters to choose from, including one that brings back the Execution Plan to allow you to tune if necessary. With a 2 week retention period set, my table had grown to 2GB in size (plans are heavy). This will vary per instance work-load and you should monitor closely if applying to a production server.

Also, avoid storing this within system databases. I’ve had this stored within a very large user database before, although a separate admin database would always be preferred.

For properly documented info whoisactive.com is the place to be. Below is my simple take on it;

# Create Log Table.
# Test sp_whoisactive Logger.
# Test Trim Period.
# Create & Schedule Agent Jobs.

Create Log Table

First thing to do is confirm what you’d like sp_whoisactive to bring back.

#- Capturing
#- Output

It’ll take a bit of time to customise perfectly the way you want it. If you have time, great!

Parameters can also be tweaked to your needs. One I have added might be of interest:
“@get_transaction_info = 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers”
For further reading on CXPACKET waits, look no further. Read this post by Nikola Dimitrijevic and it’ll explain it all!

We’re now ready to create the table. Enter your database/schema/table name and un-comment the EXEC statement when ready.

Test sp_whoisactive Logger

Again, just amend the database/schema/table name and hit go.

Test Trim Period

You don’t want your table to get noticeably large, and it may grow relatively quickly. Set the retention period to whatever you think you’ll need.

Create & Schedule Agent Jobs

1. Open up a new SQL Server Agent Job.

2. Name and categorise appropriately.

3. Jump to Steps on the left-hand menu and click on New Job.

4. Set the step name and ensure the correct database is selected (or include a USE statement to the code).

5. Apply appropriate job step logic within Advanced.
If you’d rather run deletes during a quiet period of the day, add as a separate job (this’ll change the logic of-course).

6. Once the above has been OK’d, navigate to the Schedules tab and click New.

7. Monitor by looking at the job history.

May 21, 2018