I am new to SQL Server 7 and have inherited a server built by a consultant that is no longer here. I have noticed that the system databases (master, msdb & model) are completed backed up on a nightly basis and are all set with truncate log on checkpoint. Is this the proper way to have things set up?
Thanks.
Mike
Details: MSDE 1.0 / SP4 - Windows 2000 Pro
I have a database that has Truncate on Checkpoint set for the Log file. The Log file is set to AutoGrow. Is it necessary to to run dbcc shrinkdb (or the like) to get Log file to contract? Is there any harm in not contracting the Log file? I'm looking for best efficiency and least-likely-to-fail path as DB sits 'really remote' and there is little opportunity for observation.
Does anyone have any recommendations on re-indexing? I have one table that bears the most growth. It has a clustered index. What would be a suitable data point to watch? I run a SP to save DBCC SHOWCONTIG info along with the duration of a test query, but haven't seen a clear breakover point.
TIA -RC
Can anyone assist with this problem.
Every now and then my overnight backups (backup Exec) fail due to the truncate log on checkpoint being enabled. This occasionally occurs on Master MSDB databases. I have unchecked the truncate log on checkpoint box numerous times and the backups work fine. Then mysteriously the box is checked again and the backups fail once more. I am stuck as to why this can happen. Is there a generic stored proceedure that checks this box ?
Help with this will be welcomed.
The log on one of my databases keeps filling up, even though I have it set to truncate on checkpoint. the only real difference between this database and the others on my server is that it is built from the dump of another database (on another server) where the tables are marked for replication.
I'm wondering if the fact it is built from a replicating database could be causing this. I've noticed I can't drop any of the table, even though my database isn't set to replicate (or publish).
two questions
1) Any ideas?
2) Is there anyway I can make my server realize I'm not replicating so it will let me drop those tables? (nothing in Enterprise manager indicates that my database is replicating or publishing).
Thanks,
Jim
I'm trying to make sure my database options are set up properly. Our database is used for our Decision Support System. The data is loaded in once a day. Should I have the truncate log on checkpoint on and should I limit the size that the log grows to. I just had to shrink the log. It grew so much that it took up all the space left. If I have the truncate log on checkpoint do I just have to issue the checkpoint command in order for it to truncate the transaction log?
View 1 Replies View RelatedHi Guys,
Still I have problem with the DBCC DBReindex which results in large size transaction log equivalent to the backup even after I tried the checkpoint command after this job doesn't change anything.
(as we cannot change to DBCC INDEXDEFRAG for all the indexes.
)
Is that Ok if we have a truncate log on checkpoint set to true when this job runs and make the truncate log on checkpoint to false after the job
And then run a full backup. Will that helps.
Please help.
-Anu
I want to split the data and log files of the system databases to separate hard disks. How can i accomplish this and is this a good idea to do so ?
View 4 Replies View RelatedHi,
How do I check he data file size, transaction log size and autogrowth setting of each of the system databases please?
I am not sure if sp_helpdb works for these?
Thanks
Hi,
I would like to move system databases (master, model, and tempdb) from one disk to another. What would be the best way to do it?
your help is appriciated.
Cheers
Hi,
I installed desktop edition of SQL Server7.0 but System databases are not displaying.only showing pubs and northwind only.any body help me.
Thank you
Novah
hi,
I have just started a new job and they have nightly backups of the master, msdb and model databases.
But why would you need a nightly backup of the model database? surely the model database never changes, so would a one time backup not be sufficient?
Please advise me if I am incorrect
Cheers
I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
The consultant hired as our Remote DBA for SQL Server 2005 recently changed the recovery model on our user database from Full to Simple. He explained this was necessary because the transaction log was growing very large and backing up the database when the model was set to Full was not shrinking it. Now, the master database and the msdb database are backed up daily along with our user database.
When we need to restore our user database, do we need to restore the master database and the msdb database too? What order should the databases be restored in?
We have C:, D: and E: drives on our server box. C: drive ispartitioned and is big enough only to hold Operating system files. D:and E: drives are what were supposed to be used by developers / dba'sto store / create SQL Server (system and user databases).Well, some developers installed the entire SQL Server named instanceand their system and user defined databases on the C: drive. Is thereany way to move the system databases (master, msdb, distributionetc.,) from the C: to the D: / E: drives?Appreciate any feedback.ThanksJagannathan Santhanam
View 3 Replies View RelatedAs an effort to rebuild system databases, I ran following command
start/wait C:Setup.exe /qn INSTANCENAME="DEV$SQL02" REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD="strongpassword"
I brought setup.exe file from installation cd to c drive of the server. My SQL Server is DEV and SQL Instance is SQL02. While running above command in command prompt (from C: prompt) I have an error masage that said 'setup failed' without any specific cause given for the failure.
I am doing above as part of disaster/recovery procedure in test environment.
Could anyone please shed some lights on what are the steps or what went wrong above while rebuilding master databases.
Thanks a lot
Mike
I have been attempting to detach and reattach the model database. I tried to set the Trace Flag 3608, but it is having no effect.
View 17 Replies View RelatedHi All,
I need to do a restore of all my system databases, master, msdb and model, and of course my production database.
The Windows 2003 OS and Sql Server 2005 have both been reinstalled on the server.
I understand I need to be in Single User Mode to restore the master database and I just wanted to clarify the procedure for a successful restore.
I've found this page: http://msdn2.microsoft.com/en-us/library/ms190679.aspx which has instructions for restoring a master database.
So, all I have to do is:
1. Be in single user mode
2. Using SQLCMD run:
C:> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'Z:MyBackLocationmaster.bak';
2> GO
From the instructions:
"After master is restored, the instance of SQL Server shuts down and terminates the sqlcmd process"
My Questions:
When I've restored the master do I repeat the steps above to restore the msdb and model databases?
Are there any other pitfalls?
Thanks for any advice
Melt
Hi,
i´m looking for TSQL-Code (7.0/2000) to identify, if a SQL Server Database ist a SystemDatabase or a UserDatabase. In the sysdatabases there is no information abount that.
Skol,
Peter
whenever we are moving system databases from one drive to another, do we need to move the path physically? or it automatically moves.I want to try the rebuild the sql server. So I want to corrupt the master database so, I deleted the master mdf file and restore it back but instead of database corruption it is giving the following error message. How can I corrupt the master database and can practice the rebuild the server.
And one more thing when I try to use the repair option I couldn't able to get all the things back to normal. The database engine service, replication is not working but SSAS, SSRS things are showing successfully repaired.
2014-01-10 21:34:26.44 Server Error: 26055, Severity: 16, State: 1.
2014-01-10 21:34:26.44 Server The SQL Server failed to initialize VIA support library [QLVipl.dll]. This normally indicates the VIA support library does not exist or is corrupted. Please repair or disable the VIA network protocol. Error: 0x7e.
2014-01-10 21:34:26.44 Server Error: 17182, Severity: 16, State: 1.
2014-01-10 21:34:26.44 Server TDSSNIClient initialization failed with error 0x7e, status code 0x60. Reason: Unable to initialize the VIA listener. The specified module could not be found.
[code]....
Hi all,
I'm having some performance-wise thoughts about my new sql-server 2005 installation ...
I have my SQL installed on one partition (the system dbs are also on that partition by default), my regular databases (non system dbs) are on different partition.
The question is - if my sys dbs are on different partition, could I experience some performance issues ?
One senario that I can think of is when the SQL looks for SPs starting with sp_ in the master DB, the disk will have to check a different partition. Perhaps such senario was solved using some kind of caching methods on the sql server itself.
Hope my thread is in the right forum.
Cheers everyone.
I am having trouble rebuilding the SQL 2005 system databases on my cluster.
The Cluster hardware failed completely and I am recovering from scratch (but only 1 node at this time)
The Win2K3 OS is recovered and communicating with the domain.
Cluster services have started and the quorum is online.
The clustered SQL instance is offline because there is no master.mdf present.
I run the setup command as per BOL:
start /wait setup.exe /qn VS=<VSName> INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=<StrongPassword> SAPWD=<NewStrongPassword> SQLACCOUNT=<domainuser> SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=<domainuser> AGTPASSWORD=<DomainUserPassword>
The Summary.log reports the following:
Machine : CLUSTER1
Product : Microsoft SQL Server 2005
Product Version: 9.00.1399.06
Install : Successful
Log File : C:Program FilesMicrosoft SQL ServerSetup BootstrapLOGFilesSQLSetup0001_CLUSTERSVR1_SQL.log
--------------------------------------------------------------------------------
Setup succeeded with the installation, inspect the log file completely for status on all the components.
However the Microsoft SQL ServerMSSQL.1MSSQLDATA directory is empty. There are no system databases.
Without a rebuilt Master I cannot bring the clustered SQL instance online to then restore my database backups.
Any ideas?
D.
I am in the process of moving my system databases to another volume. I have accommplished the first section pertaining to master database.
I have reached step 3 in the Resource database move section down below.
It states to change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
I changed the query to point ot the new location of the master, (E:MSSQLData)
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= E:MSSQLDatamssqlsystemresource.mdf');
I get the following error when I run the query:
Could not locate entry in sysdatabases for database mssqlsystemresource. No entry found with that name. Make sure that the name is entered correctly.
What am I doing wrong? My syntax must be incorrect. But I can't figure it. Anybody done this before.
These are the steps per msdn.
****************************************************************************************************************
To move the master database, follow these steps.
1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server 2005 Services node, right-click the SQL Server (MSSQLSERVER) service and choose Properties.
3. In the SQL Server (MSSQLSERVER) Properties dialog box, click the Advanced tab.
4. Edit the startup parameters values to point to the planned location for the master database data and log files and click Apply. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
-dC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
If the planned relocation for the master data file is E:SQLData and the planned relocation for the log file is F:SQLLog, the parameter values would be changed as follows:
-dE:SQLDatamaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lF:SQLLogmastlog.ldf
5. Stop the MSSQLSERVER service.
6. Physically move the files to the new location.
7. Restart the MSSQLSERVER service.
8. Verify the file change.
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
********************************************************************************************
To move the Resource database, follow these steps.
1. Stop the MSSQLSERVER service if it is started.
2. Start the service in minimal mode. To do this, at the command prompt, enter <SQLPath>innsqlservr -c -f -T3608 where <SQLPath> is the path for the instance of ssNoVersion. For example, C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL. This will start the instance of ssNoVersion for master-only recovery.
3. Run these queries. Change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf');
4. Make sure the Resource database is set to read-only by running this query:
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
5. Physically move the files to the new location.
6. Restart the MSSQLSERVER service.
hi
i want to retrieve all columns for all tables (for all databases) by this system sp :
Code Snippetexec sp_msforeachtable 'exec sp_columns ''?''' but when i execute this script, sql server return number of empty result set, how to solve this problem ?
thanks.
How do you hide the default system databases so that they cannot be viewed? Also when it it time for me to access them how do I unhide the databases?
View 1 Replies View RelatedHi
What are the pros and cons of putting the system databases in full recovery mode
I am asked by my manager to keep everything simple and uniform.We have all the user databases in Full recovery mode and he wants all the system databases as well to be full recovery mode.
I would like to get the points where in i can argue that, there is no point in having the system databases in full recovery mode.Unfortunately i dont see anything related to this in BOL or internet.
Plz help
Thanks
Sreenath
I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
Hello there,
I've been told that it is good practice to keep mdf and ldf files in another location... We have it in place for all our user databases, however mdf and ldf files for our system dbs are still at the same location. I was wondering what is the right way of splitting those should be?
Suddently System Databases Folder is not showing on Studio 9.0 for all the SQL servers instance. Do you know why ? Is there any option other than De-attach them?
Hi,
I was looking for system databases diagram(poster). If any one has this could you please point me to the URL where i can find it!!
Thanks in advance.
Hi all
I was just wondering on if I have to migrate the systems databases from 2000 ie. Master , Model & MSDB ??
Thanks
Hi there
I have a concern about restore whole SQL server environment.
1st server:
x86 SQL Server 2005 EE SP2 + hotfix 3159 is installed on Windows 2003 SP1 R2. SQL Server is running under Local System Account. Server is not part of AD.
2nd server
Win XP SP2, x86 SQL Server 2005 DE SP2 + hotfix 3159. SQL Server is running under Local Admin Account. Server is part of AD. Server has Credentials and linked servers
I'm trying to move all databases from server 2 to server 1
For this I am doing:
1) Create backup SMK
2) Detach all user databases
3) Backup all system databases
4) Copy all this stuff to server 1
5) Run Server 1 in single-mode (-m)
6) Restore master database
7) Try to restore backup SMK that was done on step 1 and get this error message:
Msg 15329, Level 16, State 11, Line 2
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
My understanding is that credential secrets, linked server login passwords are lost and should be recreated. DbMK can be restored using passwords.
As far as I know this can be avoided if the same domain account is using for both servers. But the question still this - if I have stand alone server, using local system account for the SQL Server service and have encrypted objects - I cannot restore SQL Server on another box (say in case disaster lost original one) without loosing credentials and linked servers even if I have all backups including backup of SMK.
Sounds gloomily for me. Any idea what I missed?
PS This links
http://blogs.msdn.com/lcris/archive/2005/09/23/473464.aspx
http://blogs.msdn.com/lcris/archive/2005/07/08/437048.aspx
http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx
http://blogs.msdn.com/lcris/archive/2006/04/10/572678.aspx
and all relative topics in BOL were studied very carefully
Regards,
Gennadi
I just finished installing SQL Server 2012 with the SQL Server Replication feature component checked. On configuring transaction replication, i notice the distribution database wasn't there. I ran the below query
EXEC sp_get_distributor And the value for distribution_db_installed is 0 as shown below
installed distribution server distribution db installed is distribution publisher has remote distribution publisher
0NULL000
Followed this article to move my model and msdb databes but I think I messed up. http://msdn.microsoft.com/en-us/library/ms345408.aspx
The instance will not start because it says one of the files does not match it's primary file (not sure if it's model or msdb)
I can bring the instance up in single user mode (NET START mssqlserver /f /t3608) but when I try to do a query to re-do my alter statements, it says i can't attach because only one seesion is allowed. in 2000 i used to be able to start query analyzer without starting enterprise manager..
When i try and start with a minimal config (sqlservr -c -m) it won't start. I get an error saying that one of my files does not match it's primary file (either model or msbd don't know which) any ideas????????