category : MS SQL

Thanks to Ola Hallengren, here is a simple scrip to report the fragmentation level of you database’s index One thing to note, if you don’t see any results after running this, you may have no index larger then 1000 pages, or no fragmentation at all ! Lucky you ! DECLARE @DatabaseID int SET @DatabaseID = ..

Read more

Small simple scrip to automate SQL Index maintenance. Of course, the best scripts to accomplish this is Ola Hallengren’s SQL Server Maintenance Solution, that you can find here But if you want quick and simple, here it is; DECLARE @IndexName varchar(255) DECLARE @TableName varchar(255) DECLARE @Frag float DECLARE @FragCount as int DECLARE @DbName as varchar(255) ..

Read more

Open SSMS (If you don’t know what is SSMS, you shouldn’t be doing this…) Paste and execute this query, modifying it to your needs USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = ‘d:\data\tempdb.mdf’) GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = ‘e:\logs\templog.ldf’) GO Restart the MS SQLSERVER ..

Read more

Make sure to run all listed commands from the database you are fixing, not from Master. List the orphaned users in the database EXEC sp_change_users_login ‘Report’ To assign/repair with an existing login: EXEC sp_change_users_login ‘Auto_Fix’, ‘user’ To create a new login and password for this user, use this instead: EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ..

Read more

Once you have you Microsoft SQL Server installed, you will need to open some port in the firewall if you intend to access it from another server/computer.  Copy and paste the following commands to open all common ports used by SQL Server: @echo =========  SQL Server Ports  =================== @echo Enabling SQLServer default instance port 1433 ..

Read more