I have restored a database on our development system to shrink down the file size being used. Originally, the database parameters show "Size=43000 MB" and Space Available = "31000 MB".
All of the files, including the LDF file, now take up a total of 8 GB of disk space, but when I look at the database properties it still shows "43000 MB" and "31000 MB".
Is there a way I can refresh this to show the actual size?
There's a sql server 2000 database that was created as a copy of another db, let's say db1 and copy_of_db1. db1 has been updated (structure and data) since copy_of_db1 was created, while copy_of_db1 has remained static. I now need to update copy_of_db1 to be in sync with db1 and use copy_of_db1 so I can drop db1. What would be the fastest and most efficient way to update copy_of_db1 to mirror the current db1?
I have been tasked with designing an automated process to restore production data to our testing environments on an as needed basis. The schedule would revolve around our software testing and deployment schedules. I'm looking for suggestions on best practices for this task in the form of advise / links to references / etc.. Instead of presenting all of my requirements here, I'll spare you that information :). Since part of it also needs to encompass data stored in Oracle (10g). I've done a several Google searches but would like to validate / invalidate my research against the advise of the experts here.
I currently have a test sql server database and I am trying to refresh it with the production sql server data - to get the production current data.
If I copy the production database.mdf file and the database.ldf file from the from the production server and replace it with the test database database.mdf file and database.ldf files and then restart the database, would this give me the production current data, please advise. - If not could you please advise on how I could get the productions current database.
I have a need to refresh a report after applying a filter on the data that is displayed, without accessing the database.
The following example is a simplified version of the functionality I'm trying to accomplish. The first report displays Sales & Projection data for all products. On clicking a particular product in the header, the second report displays just that product and the total metrics. I have created an rdl file for both the reports and based on the parameters passed, it displays one or several products. My concern is that the report is having to access the AS cube every time a product is clicked. In the real report, it's taking minutes to get the data. Is there way I can get the report to refresh to apply the filter without accessing the database again? Thanks in advance...
Hi, i use this script that show me the size of each table and do the sum of all the table size.
SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Hi, I am using exec sp_helpdb go dbcc sqlperf(logspace) for getting database size and log size. Is this gives the correct database size and log size or Is there any other way to get the logsize and database size by means of query analyzer.
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
I'm getting this error while trying to insert records into a SQL Server Compact Edition database. I have pasted my connection string that was used when creating the database as well as for accessing that same database from my Windows application.
Thanks for any help any of you can give!
Data Source=OnTheGo.sdf;Encrypt Database=True;Password=<password>;Max Database Size=4091
I am developing a smart device application with Visual Studio .Net 2005 and SQL Server Compact Edition database. And also using merge replication to synchronize the data from the mobile device to the SQL Server.
My database size is around 350MB. So when I am trying to synchronize this is the error message that I get. " The database file is larger than the configured maximum database size. The setting takes effect on the first concurrent database connection only.[Required Max Database size ( in MB; 0 if unknown)=129].
I tried changing the Max database size in the connection string and my connection string looks as follows and still did not have any luck.
I am currently refreshing the Reporting Server Database from Production database(size 200mb) every night by using backup and restore as follows:
Restore Database ProjectDB from disk = 'D:MSSQLBACKUPRptBakProjectDB.bak' with move 'ProjectDB_log' to 'd:MssqlDataProjectDB.ldf', move 'ProjectDB_data' to 'd:MssqlDataProjectDB.mdf'
I usually take Transaction Log Backup from Production every hour everyday.
I am planning to apply the transaction log every hour on Distination Server Database (7am to 9pm). Is it right way of doing it?
I am confused whether to use WITH RECOVERY OR WITH NORECOVER while restoring a database.
I am getting errors(Msg 4306) when applying the log.
Can anyone help me how to apply incremental transaction log to the restored database(step by step)?
hi my database on remote server i cannot access directly. i can access it only with query analyzer. my log file size is 9mb but nothing in database. only few tables there so how i can reduce log file size with query.
We have a SQL server database most time for read only because the data source comes from another database( which is not SQL server database, now it is about 2G). Every day we have a job running as following: Step 0. extra the data from another database, and create plain text files for each table for BCP job. Step 1. drop all of the index Step 2. truncate all of the tables. Step 3. BCP in all of the data from plain text file. Step 4. create all of the index again Step 5. shirnk the database.
Everything runs fine but the database grows 1 G from yesterday's database. I am sure we do not have so much data entry in one day. Any one can give some suggestion? I wonder if I need do shirnk the database or shirnk the data file before create the index. How can know how much size for all of the index file? Thank you very much. Judy
I am a beginner with sql and I have been inputting data (txt files) to my database and now have approached 4GB in size and it will not let me expand any further on the primary file group. My 'boss' said that sql has no size. I am using desktop
Is there a stored procedure that returns the current size of a database, and the maximum size of a database whose growth property IS NOT set to unlimited?
When I run sp_spaceused on my database it give me a total size of 258MB The backup of this database reads 226MB The Properties screen tells me that my data is a total of 278MB I am running SQL 7.0 . Why am I getting different sizes?
I have a simple question, why Am I running out of space with my current 3.5GB database, I am constantly expanding it, yet when I dump the database nightly the dump is just around 1.5GB?
Newbie here ....I started with a datbase of 90 megs...it now grew to 300 megs in 2 months...what is the best way to see the largest table or to find out why this database grew so fast? Thanks!
Hi.. My database data file are over 2.1 Gbs now. can anyone tell me is my data file too big?? or not safe?? or there's a limite for MSSQL 2000. my last choice are move some data to another database. but i have consider the report i have to make. so i really dont want to do that. can anyone advice. thanks
Hi, I have 30 servers and about 600 databases. I want to know the size of databases ( data files as will log files) both used and allocated space. Can any one tell me the easiest way to get this information?
Hi all, We are using sql6.5. srvpack 3. I noticed that one of our database was maxed out, and increased the size of the device and then increased the size of the database. when I checked the database it is still showing the database is maxed out. I did it twice, it still shows that the space has been used. Can anyone please explain to me why this is happening and how to get the actual space measurement. Thanks in advance.
After we ran sp_spaceused on one database, the size for the UNUSED column is a negative number -1324KB. Can someone explain how this could happen? I really appreciate.
I need to check the log size of my database periodically and send a notification if the size exceeds 8 GB. I want to set this up as job that runs every 30 minutes or so and page the DBA if it exceeds the above said limit. Can anyone give me the command which can be used to obtain the logsize into a variable. I am new to SQL and would appreciate your expert advise.
I want how can i do to do capacity planning in sqlserver 2000. Do you make scripts (vbs) who query the sysfiles tables and transfer the answer in excel ? Please help me. Phil
