Browsing:

Category: SQL

Move SQL Tempdb files – Common errors

Do you keep getting errors and timeouts when doing queries with sort?
My first guess wil be to check out the location of my tempdb files, is the disc space running out?db

Run query on the correct database
EXEC sp_helpfile GO
Tip: you can also view it under properties.

USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\MSSQL\DATA\tempdb.mdf'); GO

 

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\DATA\templog.ldf'); GO

Restart sql services: 'stop and start'

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

rerun the sp query to verify the correct location fort he tempdb files
Now delete the old files or in my case rename them before finally remove them.
Next we are gonna change the properties for the autogrowth.
Error cant acces properties of tempdb:

DBCC UPDATEUSAGE(tempdb)
temp_1

Important Note on common ERRORS: SQL Server doesn’t support moving TempDB Database using backup/restore and by using detach database methods.

Error Message Received when you try Backup and Restore Method

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Error Message Received when you try Detach Method

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.


Linked server setup – Back to basics (error 7399)

I recently received this question:

Why am I getting an error 'Invalid authorization specification'. I used my SA account, but i keep getting the error that the credentials specified are incorrect.

There is a very simple answer to this question, which I will share with you.

The error you get when the credentials are invalid:LSSLinked server is a connection between two servers in your network. If you want to connect the servers, the specified credentials need to be known on both servers, So, unless you use the same password for sa acounts on all your network server, which is never recommended in the first place! I advise you to create a specific Linked server sql account, for example I created a LS_2014 account on both servers and specified on which databases the Linked server is allowed to read and select data, as you can see in the example below.

ls_account

ls_acc

This keeps your databases secure and easier to manage.

You could also set it to use your current logged in account, but you need enough permissions on all servers and depending on who is using the SQL server, the linked server might not be available.

If you use the account specifically created for the Linked server and test the linked server account, the connection will be successful and you will see the table you granted read permissions on is available in the linked server dropdown.


Report server log file eating up diskspace – ReportServerTempDb

I got a 400Gb log file for my birthday today.

This 400gb made me mad, It's just a temp db. And all processes are down this morning. Looking around on the internet, this seems to be a common known problem, some even  state this problem is acknowledged by Microsoft.i can be.. engineer

After reading some blogs on possible solutions. I decided I need to solve the disk problem first. First checks you need to do before you start the troubleshoot:

You need to rule out that you didn't bring this over yourself, the ReportTempDb needs to be in Simple mode, this means there are no logbackups and the it should automatically reclaim space in your logfiles, well, this is how it should work. But in this case the logspace keeps growing. Indicating something is wrong with the reportserver, when it already is in Simple mode.

Also It could be worthwhile to check which reports are costing you, since not only your log space is invaded by report servers, your CPU is probably a victim too..

SELECT 
	sd.ReportPath,
	COUNT(s.SegmentId) as CountOfSegmentId
FROM 
	[ReportServerTempDB].dbo.Segment s 
INNER JOIN 
	[ReportServerTempDB].dbo.ChunkSegmentMapping m 
ON 
	m.SegmentId = s.SegmentId
INNER JOIN 
	[ReportServerTempDB].dbo.SegmentedChunk c 
ON 
	c.ChunkId = m.ChunkId
INNER JOIN 
	[ReportServerTempDB].dbo.SessionData sd 
ON 
	sd.SnapshotDataID = c.SnapshotDataId 
GROUP BY	
	sd.ReportPath
ORDER BY 
	CountOfSegmentId DESC;

After these checks, we have 2 routes to go, but today we'll will start with the quick and dirty one, solve the disk shortage, since all processes are put to a stop due to this 'Stay Puft' logfile. We need to shrink this beast, I should tell you, shrinking your database is pure evil and risk taking. You should never ever ever do this! ...Unless even Microsoft says it's ok. And it's just tempdata. It should've just deleted itself.

First, we are gonna run a full backup of the ReportserverTempDb, to an external location, ofcourse. Once this is done, we canstart with the clean up of the logfile.

Bring your database level to full mode, this allows you to alter the filegroups.

Right click tthe ReportServerTempDb database Go to the option shrink --> files and check the available free space and check the release unused space button.

This should give you some space, in my case, 400Gb, whoohoo, Dobby is free! Don't forget to put your ReportServertempDb back into simple mode!

You could also shrink the log file with the 'shrink file' option, but like I said earlier, it's better to avoid this option, because it could lead to faulty logfiles when you need to recover. But I case of this ReportServerTempDb the risk is low.

Now that we have got some diskspace back, we need to dig deeper to find out the cause of this log file eating up all your precious disk space. Next topic will be how we can fix this Stay Puft log file growth.


Create and restore a database with multiple secondary data files

SQL databases are all about performance, if databases grow very large, it might be a good idea to spread your files across multiple disks.

A database can consist of three files, .mdf, .ldf and .ndf, in which .ndf is a user defined secondary data file, these files can be created to split up a fast growing .mdf. For example you create 3 files, Data1.ndf, Data2.ndf, and Data3.ndf spread on three disk drives and assigned to a filegroup e.g.'fg1'. Tables can be created specifically on the filegroup 'fg1'. The queries for data from the table can be spread across multiple disks; this will improve your database performance.

Here's a script to create a database with multiple filegroups:

USE [master]
GO

/****** Object:  Database [AdventurePark]    Script Date: 01/01/2015 21:13:57 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AdventurePark')
DROP DATABASE [AdventurePark]
GO

USE [master]
GO

/****** Object:  Database [AdventurePark]    Script Date: 01/01/2015 21:13:57 ******/
CREATE DATABASE [AdventurePark] ON  PRIMARY 
( NAME = N'AdventureParkPrimary', FILENAME = N'D:\DATA\AdventurePark.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Data]  DEFAULT 
( NAME = N'AdventureParkData', FILENAME = N'D:\DATA\AdventurePark_1.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2008] 
( NAME = N'AdventureParkFiles2008_1', FILENAME = N'E:\DATA\AdventurePark_2.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2008_2', FILENAME = N'E:\DATA\AdventurePark_3.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2009] 
( NAME = N'AdventureParkFiles2009_1', FILENAME = N'E:\DATA\AdventurePark_4.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2009_2', FILENAME = N'E:\DATA\AdventurePark_5.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2010] 
( NAME = N'AdventureParkFiles2010_1', FILENAME = N'E:\DATA\AdventurePark_6.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2010_2', FILENAME = N'E:\DATA\AdventurePark_7.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [FIles2011] 
( NAME = N'AdventureParkFiles2011_1', FILENAME = N'E:\DATA\AdventurePark_8.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2011_2', FILENAME = N'E:\DATA\AdventurePark_9.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2012] 
( NAME = N'AdventureParkFiles2012_1', FILENAME = N'E:\DATA\AdventurePark_10.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2012_2', FILENAME = N'E:\DATA\AdventurePark_11.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2013] 
( NAME = N'AdventureParkFiles2013_1', FILENAME = N'E:\DATA\AdventurePark_12.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2013_2', FILENAME = N'E:\DATA\AdventurePark_13.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2014] 
( NAME = N'AdventureParkFiles2014_1', FILENAME = N'D:\DATA\AdventurePark_14.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2014_2', FILENAME = N'D:\DATA\AdventurePark_15.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'AdventurePark_log', FILENAME = N'L:\LOGS\AdventurePark_16.ldf' , SIZE = 4896KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [AdventurePark] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AdventurePark].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [AdventurePark] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [AdventurePark] SET ARITHABORT OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [AdventurePark] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [AdventurePark] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [AdventurePark] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [AdventurePark] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [AdventurePark] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [AdventurePark] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [AdventurePark] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [AdventurePark] SET  DISABLE_BROKER 
GO

ALTER DATABASE [AdventurePark] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [AdventurePark] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [AdventurePark] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [AdventurePark] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [AdventurePark] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [AdventurePark] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [AdventurePark] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [AdventurePark] SET  READ_WRITE 
GO

ALTER DATABASE [AdventurePark] SET RECOVERY FULL 
GO

ALTER DATABASE [AdventurePark] SET  MULTI_USER 
GO

ALTER DATABASE [AdventurePark] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [AdventurePark] SET DB_CHAINING OFF 
GO

 

Need a quick restore of a database with the data spread across multiple disks onto a new server, here's a simple script I wrote. Just replace name and location and you're ready to go.

USE master
GO

--ALTER DATABASE AdventurePark
--SET SINGLE_USER
----This rolls back all uncommitted transactions in the db.
--WITH ROLLBACK IMMEDIATE
--GO

RESTORE DATABASE [AdventurePark] FROM  DISK = N'\\Teletraan\sqlbackup$\AdventurePark\FULL_BU20150101_000001.bak' WITH  FILE = 1,  
MOVE N'AdventureParkPrimary'     TO N'F:\SQL AdventurePark\AdventurePark.mdf',  
MOVE N'AdventureParkData'        TO N'D:\ARCHIVEDATA\AdventurePark_1.ndf',  
MOVE N'AdventureParkFiles2008_1' TO N'D:\ARCHIVEDATA\AdventurePark_2.ndf',  
MOVE N'AdventureParkFiles2008_2' TO N'D:\ARCHIVEDATA\AdventurePark_3.ndf',  
MOVE N'AdventureParkFiles2009_1' TO N'D:\ARCHIVEDATA\AdventurePark_4.ndf',  
MOVE N'AdventureParkFiles2009_2' TO N'D:\ARCHIVEDATA\AdventurePark_5.ndf',  
MOVE N'AdventureParkFiles2010_1' TO N'D:\ARCHIVEDATA\AdventurePark_6.ndf',  
MOVE N'AdventureParkFiles2010_2' TO N'D:\ARCHIVEDATA\AdventurePark_7.ndf',  
MOVE N'AdventureParkFiles2011_1' TO N'D:\ARCHIVEDATA\AdventurePark_8.ndf',  
MOVE N'AdventureParkFiles2011_2' TO N'E:\ARCHIVEDATA\AdventurePark_9.ndf',  
MOVE N'AdventureParkFiles2012_1' TO N'E:\ARCHIVEDATA\AdventurePark_10.ndf',  
MOVE N'AdventureParkFiles2012_2' TO N'E:\ARCHIVEDATA\AdventurePark_11.ndf',  
MOVE N'AdventureParkFiles2013_1' TO N'E:\ARCHIVEDATA\AdventurePark_12.ndf',  
MOVE N'AdventureParkFiles2014_1' TO N'D:\SQL DATA\AdventurePark_13.ndf',  
MOVE N'AdventureParkFiles2014_2' TO N'D:\SQL DATA\AdventurePark_14.ndf',  
MOVE N'AdventurePark_log' TO N'F:\SQL LOGS\AdventurePark_15.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

Creating a linked server ´MySQL to MSSQL´(query the MySQL database without openquery function)

In addition to my previous linked server tutorials, I decided it is time to add MySQL to the linked server series.
In order to have the bug tracking application, Mantis migrated from linux and have it run on a windows environment, I wanted to create a replication between SQL2008 and MySQL, but then I thought, why not try out a virtual linked server again first, to test Mantis isntallation on a Windows based installation, since the online promise of Mantis on a MSSQl environment is not very promising. So today we will create a linked server from MySQl to MSSQL on a windows 2008R 64 bit environment.

Create DNS for MySQL

In order to do so, We first need to install the correct drivers in order to create a ODBC DSN, Just download the drivers from Mysql developers site http://dev.mysql.com/downloads/connector/odbc/ and install them to your database server. DriversIf we see the listed drivers, it means we can create a new DSN, so open up the System DSN tab and ADD a new DSN, you must fill in the correct credentials, for example:ODBC_Connector

Data Source Name: Enter a describing name, so you can see what it does, you might have more linked servers or other connectors running on the same server.
Description: this isnt maditory, but if you want to be more specific, be my guest.
Server: in my case,it's localhost, as this is a test server and MSSQL and MySQl are on the same server.
Insert username and password, when this is done. The database will display the possible databases you can connect to, in the dropdown.

Click OK and as you can see the System DSN has been added to ODBC.

Create new Linked Server

When this is done, it's time to open up the MSSQl server and add a new linked server to the Server Objects.4_create_LInkedsname your linked server, I give it the same name as the SystemDSN. And choose the correct provider: Microsoft OLE DB Provider for ODBC Drivers. and datasource equels DSN name.
You need to fill in all the credentials for the provider string, for example:

DRIVER=(MySQL ODBC 5.2 ANSI Driver);SERVER=localhost;PORT=3306;DATABASE=mantisbt; USER=user;PASSWORD=password;OPTION=3;

Note: meaning of OPTION=3 in the MySQL connection string:
Option=1 FLAG_FIELD_LENGHT: Do not Optimize Column Width
Option=2 FLAG_FOUND_ROWS: Return matching rows
Option=3 option 1 and 2 together

Now click OK, this is always the most fun part to me! when it says connection tot the linked server succeeded!

In addition to this, you can enable provider options on the SQLOLEDB, In my case I select the Dynamic Parameter and Allow inprocess.

Now, lets run the test and see if it connects with the databases, as you can see, it connects all the databases available on the MySQL server.

Connection test

But, most important, we can query it directly. Wheeee!

Linked server without OpenQuery function (Tip!)

Maybe you have read other MySQL linked server tutorials before this one and found out that you could only query the mysql database using the openquery() function or maybe that IS  the reason it brought you to this site. Extra, as in extra work, is never fun! With the correct ODBC driver and the right provider options, you can query the MySQl database, just like any other MSSQL database on your MSSQL server. Just follow the tutorial above and don't forget to  enable the correct provider options. Cheers!