Shrink System Databases

Apr 28, 2015

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

View 9 Replies


ADVERTISEMENT

SQL Server 2008 :: Shrink System Databases

Apr 28, 2015

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

View 1 Replies View Related

DB Engine :: Start Or Call System AUTO-SHRINK Process?

Jul 23, 2015

Is it possible to manually force/call/start the system AUTOSHRINK process? I have an issue that appears only when the engine shrinking process is running and I need this to reproduce my bug.

I know how to start a "regular" database shrink process with:DBCC SHRINKDATABASE(xxxx);, but this is not the same as one started from the database engine.

View 13 Replies View Related

Why Is My Database Re-indexing Script Not Able To Shrink Databases? (MSSQL 2000)

Aug 3, 2007

Hi,

Currently we have a variety of SQL 2000 (and 2005) database servers, we are having issues with the maintenance plan of a few SQL2000 boxes where they no longer have enough hard disk space to do a full index-rebuild on the system.

Now we want to re-build the databases indexes approximately once a week, or maybe a little less often, in the past this has worked fine with maintenance plans.

However, we now have issues because we have some databases in offline mode, and we are quite low on disk space with no plans for hardware upgrades anytime soon.
The temporary solution is to turn the index rebuilds off.

I have been working on a script that will:


Cycle through each database and within that database:

Go through each table
Run a DBCC DBREINDEX on the table
Move on to the next table
Once the reindexing of one database is complete
IF the database is not in simple mode

Backup the transaction log
Run a DBCC SHRINKDATABASE with the required amount of free space
Go to the next database until all are complete.The logic is quite simple but so far this has not worked, it would appear something is locking the transaction log until the script exits.


Now the script works fine excluding the shrinkdatabase, I always get:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Shrinking database: inf_dev target percentage: 10 at: Aug 2 2007 5:33PM [SQLSTATE 01000]
Cannot shrink log file 2 (INF_PROD_Log) because all logical log files are in use. [SQLSTATE 01000]

Where I'm indexing the INF_Prod database.
A DBCC LOGINFO shows something along the lines of:



Code Snippet
BACKUP LOG successfully processed 45162 pages in 152.607 seconds (2.424 MB/sec). [SQLSTATE 01000]
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- ---------------------- ---------------------- ----------- ----------- ------ ---------------------------
2 253952 8192 29618 0 128 0
2 253952 262144 29621 0 64 0
2 253952 516096 29620 0 64 0
2 278528 770048 29619 0 64 0
<cut cut>
2 9043968 370737152 0 0 0 29800000001528500007
2 9043968 379781120 29802 2 64 29800000001528500007
2 9043968 388825088 29801 0 64 29800000001528500007




Clearly there is something in the log file towards the end.
However, I don't know why this is happening as I'm running the script in the master database and I've backed up the transaction log of the database I'm working on.
I've tried doing Full backup + Transaction log + Shrink, it fails.
I've tried waiting 10minutes in the script + shrink, it also fails.

However, if I open a query analyzer and do a backup log, then a shrink it works perfectly every time.
However in the script it always fails no matter what I do.

Where am I going wrong here?

Regards,
Gareth

View 3 Replies View Related

System Databases

Jun 21, 2004

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 Related

System Databases

Jun 18, 2008

Hi,
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

View 1 Replies View Related

Move System Databases?

Apr 16, 2002

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

View 3 Replies View Related

System Databases Not Showing

Oct 25, 2000

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

View 2 Replies View Related

Backup System Databases

Feb 1, 2005

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

View 4 Replies View Related

Do System Databases Need To Be Restored?

Sep 11, 2007

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?

View 13 Replies View Related

How To Detach System Databases...

Jul 20, 2005

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 Related

Rebuilding System Databases

Nov 29, 2007

As 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

View 4 Replies View Related

How Can I Move The System Databases?

Oct 26, 2005

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 Related

Restore All System Databases

Feb 8, 2007

Hi 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

View 1 Replies View Related

System Databases & Truncate Log On Checkpoint

Jul 7, 1999

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

View 1 Replies View Related

Identifying System/User Databases

Nov 12, 2002

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

View 4 Replies View Related

SQL 2012 :: System Databases Moving

Jan 11, 2014

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]....

View 9 Replies View Related

System Databases On Different Disk Partition

Aug 19, 2007

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.

View 6 Replies View Related

Rebuilding System Databases On A Cluster

Jan 5, 2007

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.

View 7 Replies View Related

Moving System Databases SQL 2005

Mar 16, 2007

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.

View 6 Replies View Related

How To Retrieve All Columns Of All Databases With System Sp.

Apr 27, 2008



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.

View 8 Replies View Related

How Do You Hide The Default System Databases (i.e., Master)?

Feb 5, 2001

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 Related

System Databases In Full Recovery Mode

Mar 6, 2006

Hi

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

View 3 Replies View Related

Move Mdf Files For System Databases To Another Location

Mar 18, 2008



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?

View 5 Replies View Related

System Databases Folders Disappeared From SSMS 9.0

Jun 14, 2007

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?

View 4 Replies View Related

Sql Server 2000 System Databases Diagram(poster).

Sep 24, 2004

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.

View 2 Replies View Related

2005 Migration Do You Have To Migrate The 2000 System Databases?

Sep 20, 2007

Hi all

I was just wondering on if I have to migrate the systems databases from 2000 ie. Master , Model & MSDB ??




Thanks

View 4 Replies View Related

Restore SMK And System Databases (credentials And Linked Servers)

Apr 28, 2008

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

View 4 Replies View Related

SQL 2012 :: Replication - Distribution Database Doesn't Appear Under System Databases

Oct 30, 2014

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

View 1 Replies View Related

SQL Server 2005 Instance Is Dead After Moving System Databases!

May 20, 2008

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????????

View 10 Replies View Related

Accidentally Populate Tables Into System Databases/master Database

Jul 31, 2007

Hi,

I accidentally populate tables into System databases/master database. What should I do? Should I delete all the tables I populate in mster database?

Thank you very much!

View 4 Replies View Related

Best Disaster Recovery Plan For System Databases(Master,MSDB...)

Nov 20, 2007

Hi all

What is best Disaster/Recovery methode to securing the system databases.

Master database allows only Full backup
MSDB allows Full ,Diff

View 5 Replies View Related

SQL Server Admin 2014 :: Changing Compatibility Level Of System Databases

Jun 27, 2014

I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved