Best method for sharing a DB ??
-
What is the best method for sharing a DB.
I have reduced the size of the DB to 50 records, and compressed it, but it is still 4 GB !
I obviously can't send 4 GB.
Any suggestions ?
Thanks
-
Is that the size of the data file, log file or both?
You can try running a backup and compressing it , this may be smaller
Though having a database with only 50 records that is 4gig sounds very unusual, how many tables does your database contain?
-
deleting records so there are only 50 left does not shrink the size of the database....the database will keep it's original size, so that it is ready to insert more data.
you'll want to explicitly shrink the database to get it small enough so you can pass it over for testing:
SSMS, right click on the database in question...
Tasks...
Shrink...
Database...
That should make it smaller so you can give it to the dev tream or whatever.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ifila (8/3/2009)
What is the best method for sharing a DB.I have reduced the size of the DB to 50 records, and compressed it, but it is still 4 GB !
I obviously can't send 4 GB.
Any suggestions ?
Thanks
The best way of sharing a DB is to put the database on a server that everyone has access to and grant access to the users that should have access. That is the whole purpose of using a database. 😉
Why do you need to 'share' the database by sending a copy to someone? What are you going to do when they make changes and want to 'share' those changes with you?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I need to send a testDB to another country, so they may test a new project that was developed.
I dont want a direct connection to my server from another country.
-
ifila (8/3/2009)
I need to send a testDB to another country, so they may test a new project that was developed.I dont want a direct connection to my server from another country.
Ah yes, that makes sense but I would not consider that 'sharing' a database. 🙂
Okay, to send someone a copy of your database you can back it up and send the backup file. The backup will only be as large as is required to hold the amount of data in the database.
Note, when they restore the database it will be the size you have it setup for. I would recommend shrinking the file to an acceptable size before backing it up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
DBCC SHRINKFILE (microsoft525_log, 10) WITH NO_INFOMSGS
I am using this query and the shrink in Tasks and it will not got under 5GB for 49 records ?
When doing a copy, why can you only change the file location and not the size with the wizard ?
Any way around this ?
-
shouldn't that be DBCC SHRINKDATABASE(N'YourDataBaseName' ) instead of shrinkfile? I'm thinking that if you deleted a lot of data, the space is reserved in the MDF, and not in the log; or maybe the database is in FULL backup mode and not simple, and has not been backed up?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (8/3/2009)
shouldn't that be DBCC SHRINKDATABASE(N'YourDataBaseName' ) instead of shrinkfile? I'm thinking that if you deleted a lot of data, the space is reserved in the MDF, and not in the log; or maybe the database is in FULL backup mode and not simple, and has not been backed up?No, you really should be using SHRINKFILE on both files. That way, you have control over the end result and can shrink the file to a specific size.
ifila: You need to shrink the data file - not just the log file.
And can you explain what you mean by copying? What steps are you taking to 'copy' the database?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Tasks -> Copy Database ->
i have tried doing a Full backup then going the SHRINKFILE and the other way around but get the same result!
I am shinking both the log and data files.
-
Can you run the following queries for me - and post the results?
Use {your database here};
SELECT * FROM sys.indexes;
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, 'DETAILED');
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Here ya go.
Thanks
-
I don't see the information from sys.indexes - but that is okay. What I do see is that you have a HEAP (object id 1215343394) that could be taking up a lot of space. This object does not have a clustered index - and is probably taking up additional space.
Try creating a clustered index on those two tables that are identified as HEAPS - and also try rebuilding the indexes on the other table.
Once that is done - you can remove the clustered indexes, but you really should have identified this and keep it anyways. You should be able to shrink the data file after that has been done.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
The other thing I haven't seen mentioned is the relative size of the data and log files and the recovery mode. If the database isn't already in Simple recovery set it to that before trying to do the shrink and make sure all transactions are completed, otherwise the log file won't be able to be shrunk much if at all.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply