SQL server Move user database steps and scripts: Check you have a free space on the moving drive and make sure the SQL service account has read and write permission. (You can check the account name in configuration manager) Move TempDB database --========================= for tempdb -- note the existing details with logical name --Step 1 USE TempDB GO EXEC sp_helpfile tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL11.PROD\MSSQL\DATA\tempdb.mdf templog 2 C:\Program Files\Microsoft SQL Server\MSSQL11.PROD\MSSQL\DATA\templog.ldf --Step 2 -- alter to new location USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'T:\SQL_TEMPDB\tempdb.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'T:\SQL_TEMPDB\templog.ldf') GO --Step 3 -- restart…
-
-
Msg 8909, Level 16, State 1, Line 1 Table error: Object ID index ID partition ID
Being a DBA , you will see database corruptions in your environments. I got four or five and not affected much of the data. Most of the time logical corruption and memory errors. The bellow one I got sometimes back and the worst part is backup also corrupted database. What we can do when we got a corruption errors or tickets. Analysis the error from the error log, if you have good entries or run CheckDB and get the messages. (Don’t go and detach the database, restart SQL or run repair_allow_data_loss) dbcc checkdb ('DB') with no_infomsgs,all_errormsgs I take a help of http://www.sqlservercentral.com/articles/Corruption/65804/ Gail shaw’s article to start with it.…
-
SQL server change single user mode or backup or recovery model of all database sql script
Following is the DBA script which generally used for most cases the action needed for all the databases like migration, upgrade etc. Change the database recovery model and set to single_user mode. Backup of all databases. -- Dynamic SQL select 'alter database ['+name+'] set single_user with rollback immediate' , * from sys.databases where database_id not in (1,2,3,4)-- exclude system DBs select 'alter database ['+name+'] set multi_usere' , * from sys.databases where database_id not in (1,2,3,4)-- exclude system DBs select 'alter database ['+name+'] set recovery simple' , * from sys.databases where database_id not in (1,2,3,4)-- exclude system DBs select 'backup database ['+name+'] to disk = ''B:\SQL_backup\'+name+'_full.bak''' , * from sys.databases where…
-
Test connection failed because of an error in initializing provider. Oracle client and networking components were not found
You may experience the following error. When a developer installed oracle client and tested the visual studio BIDS packages ask you to fix this. We know the MS only has a visual studio BIDS 32 bit. Recently, It asked in the forum, I had managed Oracle sometimes and had same kinda issue couple more times–> https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b6efab4-c26f-45cd-aca6-4ba3d53f13a4/the-oraoledboracle1-provider-is-not-registered-on-the-local-machine?forum=sqlintegrationservices How can we find which bit of oracle installed on: How to Tell if Oracle Client is 32 or 64 bit installed on Windows Errors: 1) Test connection failed because of an error in initializing provider. Oracle client and networking components were not found.These components are supplied by Oracle Corporation and are part of the…
-
How to use configure Ola Hallengren SQL server Maintenance script
Ola Hallengren Maintenance Plan Deployment Many of DBAs already started using the Ola hallengren’s maintenance plan script. It is a free script replacing traditional GUI maintenance plan. It is very good script for all type and variety of database, application and size. He is a Microsoft award winner for his maintenance plan script. Here is the steps, how to use effectively for many servers. You can use a CMS if you registered it or one be one. Here is what, I have customized it, you need to change the DB name and backup path in all the script, copy it notepad use Ctrl+H and replace it. Run the 1_MaintenanceSolution – Change the…