December 10, 2009 at 7:46 pm
Hi,
We have SQL Server 2005 Desktop Engine(SQLServerView) install at C:\ drive and since it grew over 4GB by 2 or 3 days we want to migrate to another disk drive which have more diskspace. Can SQL server provide runtime configuration to change another disk drive without re-install the SQL server? Do SQL Server have this configuration to change system product directory(besides user data file and log file) and would you reveal the procedure also? Thanks a lot.
Best regards,
Wallace
December 10, 2009 at 8:29 pm
You could detach the database, copy the files to the new location, then re-attach the database.
Since you are using Desktop Engine, and you are experiencing rapid db growth, you should consider upgrading the database to at minimum Standard edition of SQL Server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 10, 2009 at 11:54 pm
Thanks for your reply.
Actually this SQL server Desktop Engine file grow to 3 to 4 GB while our current data file only 100 to 200 MB. Is it bulky operation(i.e. Bulk delete data) would cause this Desktop Engine file grow extremely large?
I think it may related to our real time merge replication.
We are using SQL Server 2005 Enterprise Edition with SP2 + Windows Server 2003.
Thanks a lot
Best regards,
Wallace
December 11, 2009 at 12:25 am
When you say "Desktop Engine File," what are you actually referring to? Do you mean the log file, are you referring to a witness server that is monitoring your custom merge replication, or is it entirely something else?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 11, 2009 at 1:01 am
CirquedeSQLeil (12/11/2009)
When you say "Desktop Engine File," what are you actually referring to? Do you mean the log file, are you referring to a witness server that is monitoring your custom merge replication, or is it entirely something else?
The Database name is ServerViewDB(By default) and it's data file and log file are ServerViewDB.mdf and ServerViewDB_Log.ldf respectively.
This can be seen at SQLServer Management Studio when click 'Connect' -> 'Database Engine' -> select '<machine name>/SQLSERVERVIEW'. Is it possible to detach and attach this db to another directory? Thanks a lot.
Best regards,
Wallace
December 11, 2009 at 9:18 am
Those steps are a heirarchical structure for getting to your database server. The database engine is the guts of your RDBMS. It does not grow - it is the compiled program.
As for your large file, which one is the large file - the ldf (log) or mdf(data).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 12, 2009 at 3:48 am
CirquedeSQLeil (12/11/2009)
Those steps are a heirarchical structure for getting to your database server. The database engine is the guts of your RDBMS. It does not grow - it is the compiled program.As for your large file, which one is the large file - the ldf (log) or mdf(data).
The ServerViewDB.ldf grew to 4GB, in which over 90% are in used and cannot be shrinked down to reduce space, while ServerViewDB.mdf is only 200 MB.
We have tried to detach and attach the whole ServerViewDB to another drive which have more disk space and see any problems happened. Can SQL Server install all it's components to the disk drives except from C: drive at the first time? Thanks a lot.
Best regards,
Wallace
December 12, 2009 at 11:47 am
What recovery model are you using?
If full, you should do a full db backup and then schedule transaction log backups.
Once the transaction log has been backed up, you will see your free space in the ldf grow.
A consideration for this, find out what caused it to grow like that in the first place. Re-indexing, data loads, out of whack processes, or heavy #temptable usage could all be culprits. Fix that process and then right-size your tran log.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 13, 2009 at 6:22 pm
CirquedeSQLeil (12/12/2009)
What recovery model are you using?If full, you should do a full db backup and then schedule transaction log backups.
Once the transaction log has been backed up, you will see your free space in the ldf grow.
A consideration for this, find out what caused it to grow like that in the first place. Re-indexing, data loads, out of whack processes, or heavy #temptable usage could all be culprits. Fix that process and then right-size your tran log.
Our user database is using simple recovery model but that Desktop Engine database(ServerViewDB) is using full recovery model(by default). The transaction log of our user database is only within 1 GB but that transaction log of ServerViewDB grew to 4GB. Can that ServerViewDB do a regular transaction log backup also? Any suggestion of how to track the root cause of this sudden growth of transaction log file? Thanks a lot.
Best regards,
Wallace
December 13, 2009 at 6:49 pm
Chan Wai Yin (12/13/2009)
CirquedeSQLeil (12/12/2009)
What recovery model are you using?If full, you should do a full db backup and then schedule transaction log backups.
Once the transaction log has been backed up, you will see your free space in the ldf grow.
A consideration for this, find out what caused it to grow like that in the first place. Re-indexing, data loads, out of whack processes, or heavy #temptable usage could all be culprits. Fix that process and then right-size your tran log.
Our user database is using simple recovery model but that Desktop Engine database(ServerViewDB) is using full recovery model(by default). The transaction log of our user database is only within 1 GB but that transaction log of ServerViewDB grew to 4GB. Can that ServerViewDB do a regular transaction log backup also? Any suggestion of how to track the root cause of this sudden growth of transaction log file? Thanks a lot.
Best regards,
Wallace
Do you need to be able to recover to a point in time on the ServerViewDB?
If not, then changing it to Simple Recovery Mode may be the right choice in this scenario.
In full recovery mode, you can schedule a transaction log backup to occur on intervals to meet your needs (15min, 1hr, or more or less frequently depending on use).
Setup some alerting for file growths or you can disable autogrow and see what broke (not a very good path), or profiler, trace etc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 13, 2009 at 7:28 pm
CirquedeSQLeil (12/13/2009)
Chan Wai Yin (12/13/2009)
CirquedeSQLeil (12/12/2009)
What recovery model are you using?If full, you should do a full db backup and then schedule transaction log backups.
Once the transaction log has been backed up, you will see your free space in the ldf grow.
A consideration for this, find out what caused it to grow like that in the first place. Re-indexing, data loads, out of whack processes, or heavy #temptable usage could all be culprits. Fix that process and then right-size your tran log.
Our user database is using simple recovery model but that Desktop Engine database(ServerViewDB) is using full recovery model(by default). The transaction log of our user database is only within 1 GB but that transaction log of ServerViewDB grew to 4GB. Can that ServerViewDB do a regular transaction log backup also? Any suggestion of how to track the root cause of this sudden growth of transaction log file? Thanks a lot.
Best regards,
Wallace
Do you need to be able to recover to a point in time on the ServerViewDB?
If not, then changing it to Simple Recovery Mode may be the right choice in this scenario.
In full recovery mode, you can schedule a transaction log backup to occur on intervals to meet your needs (15min, 1hr, or more or less frequently depending on use).
Setup some alerting for file growths or you can disable autogrow and see what broke (not a very good path), or profiler, trace etc.
Thanks for reply. BTW, what's the default recovery model for this ServerViewDB? (since we observe that UAT use Simple Recovery Model and PROD use Full Recovery Model)
Best regards,
Wallace
December 13, 2009 at 8:16 pm
The default will depend on your server configurations. If your model DB is set to full recovery - then you will get full recovery when you install your DB.
As for DB specific default settings - it is possible that your install script for the db has a different default for the database specified. To find out if the DB has a default, you should contact the vendor and ask them.
Another possibility as for the mismatch: somebody on the project changed the recovery model in your UAT environment to simple recovery mode.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 13, 2009 at 11:50 pm
CirquedeSQLeil (12/13/2009)
The default will depend on your server configurations. If your model DB is set to full recovery - then you will get full recovery when you install your DB.As for DB specific default settings - it is possible that your install script for the db has a different default for the database specified. To find out if the DB has a default, you should contact the vendor and ask them.
Another possibility as for the mismatch: somebody on the project changed the recovery model in your UAT environment to simple recovery mode.
Our model DB is set to Full Recovery model by default. I found that the recovery model setting changed from Full to Simple because I detached the ServerViewDB from C: drive to another drive and when I attach it again, I was surprise that the recovery model change from Full to Simple automatically. Therefore some of the system backup jobs on ServerViewDB inside cannot run since in Simple recovery model. BTW, besides recovery model, any DB specific setting that should be awared after detach and attach this ServerViewDB? I observe that there exists some log backup job already running for ServerViewDB but the ServerViewDB.log file is still very large, and perhaps after performing transaction log backup the ServerViewDB.log is still very large, and seems that now detach and attach to another drive is our current most easiest solutions.
Thanks and regards,
Wallace
December 14, 2009 at 2:25 am
Chan Wai Yin (12/13/2009)
CirquedeSQLeil (12/13/2009)
The default will depend on your server configurations. If your model DB is set to full recovery - then you will get full recovery when you install your DB.As for DB specific default settings - it is possible that your install script for the db has a different default for the database specified. To find out if the DB has a default, you should contact the vendor and ask them.
Another possibility as for the mismatch: somebody on the project changed the recovery model in your UAT environment to simple recovery mode.
Our model DB is set to Full Recovery model by default. I found that the recovery model setting changed from Full to Simple because I detached the ServerViewDB from C: drive to another drive and when I attach it again, I was surprise that the recovery model change from Full to Simple automatically. Therefore some of the system backup jobs on ServerViewDB inside cannot run since in Simple recovery model. BTW, besides recovery model, any DB specific setting that should be awared after detach and attach this ServerViewDB? I observe that there exists some log backup job already running for ServerViewDB but the ServerViewDB.log file is still very large, and perhaps after performing transaction log backup the ServerViewDB.log is still very large, and seems that now detach and attach to another drive is our current most easiest solutions.
Thanks and regards,
Wallace
We found that there's two NT Services: MSSQL$SQLSERVERVIEW and SQLAGENT$SQLSERVERVIEW, in which both set to automatic. However when we reboot the whole server, only MSSQL$SQLSERVERVIEW service is started but not SQLAGENT$SQLSERVERVIEW, hence the job inside SQLSERVERVIEW cannot run and the ServerViewDB.log grew very large continuously. Do you have any hints on this sympthon why the SQLAGENT$SQLSERVERVIEW service cannot be started(or is it necessary to be started)? Thanks a lot.
Best regards,
Wallace
December 14, 2009 at 3:11 am
Chan Wai Yin (12/14/2009)
Chan Wai Yin (12/13/2009)
CirquedeSQLeil (12/13/2009)
The default will depend on your server configurations. If your model DB is set to full recovery - then you will get full recovery when you install your DB.As for DB specific default settings - it is possible that your install script for the db has a different default for the database specified. To find out if the DB has a default, you should contact the vendor and ask them.
Another possibility as for the mismatch: somebody on the project changed the recovery model in your UAT environment to simple recovery mode.
Our model DB is set to Full Recovery model by default. I found that the recovery model setting changed from Full to Simple because I detached the ServerViewDB from C: drive to another drive and when I attach it again, I was surprise that the recovery model change from Full to Simple automatically. Therefore some of the system backup jobs on ServerViewDB inside cannot run since in Simple recovery model. BTW, besides recovery model, any DB specific setting that should be awared after detach and attach this ServerViewDB? I observe that there exists some log backup job already running for ServerViewDB but the ServerViewDB.log file is still very large, and perhaps after performing transaction log backup the ServerViewDB.log is still very large, and seems that now detach and attach to another drive is our current most easiest solutions.
Thanks and regards,
Wallace
We found that there's two NT Services: MSSQL$SQLSERVERVIEW and SQLAGENT$SQLSERVERVIEW, in which both set to automatic. However when we reboot the whole server, only MSSQL$SQLSERVERVIEW service is started but not SQLAGENT$SQLSERVERVIEW, hence the job inside SQLSERVERVIEW cannot run and the ServerViewDB.log grew very large continuously. Do you have any hints on this sympthon why the SQLAGENT$SQLSERVERVIEW service cannot be started(or is it necessary to be started)? Thanks a lot.
Best regards,
Wallace
just 1 more additional info:
SQLSERVERVIEW jobs:
JobServerViewClear_xacta_tables (run every 1 hour)
JobServerViewDaily (run every day)
JobServerViewHourly (run every 1 hour)
JobServerViewLongInterval (run every 1 week)
Is it necessary for the above jobs run regularly? If so, SQLAGENT$SQLSERVERVIEW service should be kept started permanently.
Thanks and regards,
Wallace
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply