How do I purge data off of an MSDE database. I only want to keep 6 months of data in the database. Right now I have data going back to 2004. I get errors about every 10 seconds. "Primary File Group is Full" is the error I am getting.
Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis? If I simply delete all the records the identities for the tables do not revert back to 1.
View 2 Replies View RelatedDoes anyone know how to purge old data in a MS SQL server 2000database?
View 1 Replies View RelatedWhen connecting to an SQL Server (v7 in this case) the log file shows that old and deleted databases are still being opened as part of the process. Further, these db names are now reserved and cannot be reused, even thought I've deleted them.
Is there any way I can purge all traces of these deleted db names?
You might have guessed, I'm a newbie, which is why there are a few dozen deleted DBs.
Thanks for any help
Max
atomax@gmx.net
Regarding SQL Server data, I am looking to implement the beset Data-Archive and Purge policy. Normal, we do SQL Backups and keep the history for some period , for example, 8 weeks, so we can go back and restore any data point in time upto 8 month in past. and we also do Tape backups.
Question is Where can I get nice article or documentation on this to best design such policy where I make sure that I am covered for point in time recovery of database (which is sql backups) and point in time recovery in far past, say, 3 years ago using tape backups, and I need to make sure that I don't repeat the same efforsts.
Any advices or suggestion on this topic.
Thanks,
I have 6 tables which are very huge in row count and records needs to deleted which are older than 8 days.
Little info: Every day, 300 Million records are inserted in below 7 tables. we should maintain only 8 days worth of data in below tables. How to implement Purge script which can delete records in all tables in the same time and with optimized parallelism.
Master table which has [ID],[Timestamp]
Table Name: Sample - 2,578,106
Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table. So the records needs to deleted based on Min(ID) from Sample
dbo.ConnectionDB - 1,147,578,048
dbo.ConnectionSS - 876,458,321
dbo.ConnectionRT - 118,133,857
dbo.ConnectionSample - 100,038,535
dbo.Command - 100,032,235
Hi all,Here is my situation.I have an application that uses SQL 2000. The client has a programthat uses MSDE. The client MUST have the MSDE version of the Database.What I have done is to create a SQL 2000 Database that is exactly likethe MSDE version. What I wish to do, is at regularly scheduledintervals, update the SQL 2000 Data with the newest MSDE Data.On my clients server, they have both MSDE and SQL 2000 installedthe msde has a user name and password to access it, whereas the SQL2000 uses authenticated mode to access it.What I'm trying to do is to write a bunch of SQL Queries, that I canrun that will copy over the new Data, but I'm having problems accessingthe msde database in query analyzer from the SQL DatabaseCan anyone give me hints on the best way to try thisThanksScott
View 1 Replies View RelatedHow to implement a optimal archival and purging in MSSQL SERVER
databases
hi !
I have a job that creates tranlog backups every 15 mins and makes a full database backup at midnight.
I want to purge the old logs after the full backup , how can I do that ?
thanks
Sami
Hi DB Experts
I have a question on shrinking tempDB. Currently, I am using MS SQL 2005 Server, I have a software that used SQL DB but I had created a separate database to store my data. The tempDB is totally not used at all.
Problem - Whenever I export data into my created database on MS SQL server, tempDB also grew, I noticed that the files grew so large that it crashed the server. I am running 50GB free space on my drive where by the MS SQL server was installed.
Question - May I know are there any solution to shrink or freeze the growth of tempDB size?
Best regards
TEWCT
Hi,
In one of our DR Servers we have configured Custom Logshipping. In the folder where the .TRN files are getting copied there is a script to purge the files which are older than one day. Following is the code for the same.
@Echo Off
if exist filelist del filelist
date /t > rundate
for /f "tokens=2* delims= " %%i in (rundate) do set rundate=%%i
for %%i IN (*.trn) do echo >> filelist %%i %%~ti
for /f "tokens=1,2* delims= " %%i in (filelist) do if not %rundate% equ %%j del %%i
:pause
:Exit
Instead of removing the files older than 1 day, I need to keep 3 days transaction logs.
Being a novice I don't have much idea how to accomlish it. Can anybody help me with this?
Many thanks in advance,
Sandhya
My transmission queue has lots of messages that will never, ever be delivered because the transmission_status = "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."
How can I purge the transmission queue to get rid of this junk?
Hi,
I'm using SQL2K with SP4 2187. I have created a DB Maintenance wizard where the purging older than 1 day is set.
However, this feature seems not to be working, even if I tried two ways. Delete the scheduled job and recreate it - not successful, 2nd) delete the Maintenance Plan, still not successful.
Is this a bug or do I miss something here.
Hi,We have a Java application that runs against a variety of backendsincluding Oracle and MSSql 2000 and 2005. Our application has ahandful of active tables that are constantly being inserted into, andsome of which are being selected from on a regular basis.We have a purge job to remove unneeded records that every hour "DELETEFROM <tableWHERE <datafield< <sometimestamp>". This is how we arepurging because we need 100% up time, so we do so every hour. Forsome tables the timestamp is 2 weeks ago, others its 2 hours ago. Thedate field is indexed in some cases, in others it is not... theDELETE is always done off of a transaction (autoCommit on), butexperimentation has shown doing it on one doesn't help much.This task normally functions fine, but every once in a while theinserts or counts on this table fail with deadlocks during the purgejob. I'm looking for thoughts as to what we could do differently orother experience doing this type of thing, some possibilities include:- doing a select first, then deleting one by one. This is apossibility, but its SLOW and may take over an hour to do this so we'dbe constantly churning deleting single records from the db.- freezing access to these tables during the purge job... our appcannot really afford to do this, but perhaps this is the only option.- doing an update of an "OBSOLETE" flag on the record, then deletingby that flag... i'm not sure we'd avoid issues doing this, but its'an option.The failures happen VERY infrequently on sql2005 and much morefrequently on sql2000. Any help or guidance would be mostappreciated, thanks!Bob
View 3 Replies View Related
hi All,
I have to remove non-useful and duplicate records containing NULL , Blanks and extra spaces (either on left or right side of the column values) etc from all the tables in my server's DB XYZ weekly thru a a scheduled job with the help of a Stored Proc, that s i guess called Purging og DB. Plz help how i can do it with T-SQL.
Also i have to find out and remove all the duplicate DB objects(tables) from the DB .e.g. a table existing with name TABLE_TEST or TABLE_DEBUG etc for an original table TABLE , making sure no any of the base table is dropped.
Plz help me reagrding these two problems.
Thanks in advance for the quick replies.
Mohit
HiWe have need for an SSIS package that would perform routine purging of the growing data in some of the tables used to support notification services. While running Sql in a regular job would seem to suffice, an SSIS package would be more in line with the other processes for the alerts in terms of manageability. The SSIS package should follow the following guidelines:Deletes records from a given number of tables, based on a specified date column for each table and a specified number of days for each table, or other conditions. SystemAlertQueue: 30 days old based on the SubmitTimestamp column. SystemAlertChron: 30 days old based on the EventTimestamp column. SystemAlertNotifChron: 30 days old based on the NotifTimestamp column. WMICheck, related tables (based on WMICheckID, see WMIAlerts database diagram in SqlServer) 15 days old based on BeginCheckDate column. Each table€™s deletion routine should be distinguishable in the package. Does any body know how to do this.. Please help meRegardsDeepu M.I
View 1 Replies View RelatedI have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which I have heard works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.
Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get the following error:
Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Of course, some of the entries will be read only, since doctors have to sign off the charts and are not allowed to subsequently change them. But I should still be able to switch over to sql 2005?!?!?!?
Or... is there a way to attach the databases to SQLExpress manually?
Help pls?
After my upgrade from MSDE SP 4 to SQL express (backup and restore) my application will not see the data any more.
If I use my application (VB Dot.NET 1.x ; DB= SQL Express) to add new data (rows) this new rows are visible.
Both old and new datas are seen in SQL management studio express.
If i add rows with the SQL management studio express the appliction will not see this rows.
In both cases I connect with sa - account. (Administrator even doesn't work). I search for any restrictions on row basis but i wonder why the management tool display all rows.
Any ideas!
Hi All,
Is it possible for someone to open a msde data file in an editor (text or hex) and modify the data, then save the file back to the filesystem?
I would think this would not be allowed but I want to confirm or see if anyone has had any experiences of someone doing something like this?
Thanks.
Chris
I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which the company says works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.
Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get an error:
Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Or... is there a way to attach the databases to SQLExpress manually?
Help pls?
Downloaded and installed MSDE and just finished creating a
website using the Community Starter Kit. I'm used to working with Access
Databases and this is my first experience with anything SQL. Can someone
provide my with some guidance as to what I need to do to upload the MSDE
Database to a server. Can I just upload the files like an Access Database
or is this more involved. Thanks for you help.
I'm using Visual Studio.Net enviroment, i have SQL desktop Engine installed and everything is working great. I have a problem that whenever i create a new database connections i can NOT delete it. I know that i have to select the node containing the connection and then press the DEL Key, but it seems that the delete option is not valid!
Any Suggestion?
OK
I've loaded MSDE and its running OK
I've run config.exe to install the sample .net databases,
......but I cannot find northwind or anyother db on the server!
I've never done any db work befor so may be I'm being a bit of a tit!!! sorry in advance if i am...
stuart
Hi, I am new to .net, I installed .net sdk and framework.
now I downloaded MSDE and installed it, I got this directory : C:MSDERelA
but when I double click in the setup file nothing happened, please help me on what should I do .
thanks
Hi,
I installed MSDE and the sample databases with it. They work fine.
Well I am using the server explorer of visaula studio standard 2002. I can see all the sample databases there and connect to them as well.
1.
--------------------------------------------------------------------
Now i created a new database named "testDb" and in a similar fashion i am using windows login thingy.
When i write my connection string in the program now it doesnt work
string connectionStr =@"Server=IMRANNetSDK;" + "Integrated Security=SSPI;" + "Connection Timeout=7;" + "Database=testDB;";
If i replace my Database clause to Northwind it works absolutely fine.
The ERROR it gives is:
Cannot open database requested in login 'testDB'. Login fails. Login failed for user 'IMRANASPNET'.
Why is this happenning? :(.
============================================
2.
-------------------------
How can i configure the MSDE to use the SQL Authentication.
It doesnt allow me to do that?
Thanx Heaps,
Imran
I'm running Windows 2003 .NET server (1
year trial) and I have the latest version of MSDE installed. I have
WebMatriX II installed.
I want to create an MSDE database I can use with a DSN so can use in .ASP or APS.NET.
I've successfully created a test
database (in Webmatrix II) via Windows Authentication security and have
created a DSN via ODBC/Windows Authentication security but don't know
how to access in ASP or ASP.NET web page.
All the SQL databases I use in .ASP have a DSN, User, Password as connection parameters. I want to continue this process.
I ran the command list: setup
SAPWD=**secret** SecurityMode=SQL after setting up MSDE SP3. I log into
the server as "Administrator" with a password.
Question:
1. What am I supposed to use as a ID and
Pass if I want to setup a MSDE database in WebMatrix II using SQL
Server authentication? Everything I try fails. I was never asked to
provide a beginning ID and Pass for SQL(MSDE) Server.
2. What does the connection string look
like to access the database in ASP.NET for Windows Authentication
security? Like this: ConnectionString= "server='localhost';
trusted_connection=true; database='ichapter_test'".
3. What would it look like in .ASP?
Thanks in advance!
JurekWorks
HiI am just starting my first .NET project using the web matrix and an MSDE database. I am pretty much at the limit of my knowledge - all very interesting. I have some data in an Access spreadsheet (6 columns and 365 rows) which in the past I have copied and pasted into an Access database table. How do I get it into a MSDE database table? This process does not need to be automated/programmed as I will only have to do it once a year. Any ideas on the easiest way forward would be much appreciated.Thanks in advance.Mike
View 2 Replies View RelatedI'm trying to build a record update form and I'm
going off the "Build Your Own ASP.NET Website" book. I'm not getting
any compilation errors; the database just doesn't update.
void Update(Object s, EventArgs e) {
SqlConnection upConn = new SqlConnection(
"Server=****\netsdk; database=CLASSIFIEDS; User ID=****; Password=****;");
SqlCommand upCmd;
upCmd = new SqlCommand("UPDATE CLASSIFIEDUSER SET
ITEMHEADLINE=@ITEMHEADLINE, ITEMDESCRIPTION=@ITEMDESCRIPTION,
CONTACTNAME=@CONTACTNAME, EMAIL=@EMAIL, HOMEPHONE=@HOMEPHONE,
WORKPHONE=@WORKPHONE, CELLPHONE=@CELLPHONE, BUILDING=@BUILDING,
ROOM=@ROOM, PRICE=@PRICE WHERE ID=@ID", upConn);
upCmd.Parameters.Add("@ITEMHEADLINE", headline.Text);
upCmd.Parameters.Add("@ITEMDESCRIPTION", description.Text);
upCmd.Parameters.Add("@CONTACTNAME", contact.Text);
upCmd.Parameters.Add("@EMAIL", email.Text);
upCmd.Parameters.Add("@HOMEPHONE", home.Text);
upCmd.Parameters.Add("@WORKPHONE", work.Text);
upCmd.Parameters.Add("@CELLPHONE", cell.Text);
upCmd.Parameters.Add("@BUILDING", building.Text);
upCmd.Parameters.Add("@ROOM", room.Text);
upCmd.Parameters.Add("@PRICE", price.Text);
upCmd.Parameters.Add("@ID", Request.QueryString["id"]);
upConn.Open();
upCmd.ExecuteNonQuery();
upConn.Close();
Response.Redirect("preview.aspx?id=" + Request.QueryString["id"]);
}
The Response.Redirect works so I'm thinking there must be an SQL syntax error or something but I can't see it.
I have installed the MSDE from Office 2002 CD and MSDE is running. I can see the SQL Server Icon in tray with green arrow. CAn ne body help me how to create database in the MSDE. I am new to this. Is there any query manager or entrprise tool by which I can create database and table in it. Also I am not able ro install the Starter Kit .Please help me in this.
View 1 Replies View RelatedHi all
I have MSDE installed on my machine to support off-line use of a web application.
The database (MDF) file in question is stored on an encrypted area of my hard drive, which must be unlocked each time the machine starts up by the user entering a password.
Unfortunately, by this time the SQL Server service has already started, and although it still lists the database along with master, etc, it cannot access it. I believe the error mentions that some files are unavailable.
Stopping and restarting SQL Server using Service Manager fixes this problem. However, this can only be done when logged in as Administrator. I need to find a solution for normal users without Admin privileges.
Is there any other way of making the now-unlocked MDF file available to SQL Server? I thought about using SQLDMO to Refresh databases, but no joy.
This is a real show stopper for my application, which I've spent three years developing! It goes without saying that I'm writing this message with a long face. If anybody has any suggestions I would be very happy to hear from you!
Best wishes
Rob
I installed MSDE on a pc with no problems, but now I realise I want to create another instance and set DISABLENETWORKPROTOCOLS=1 so I can use Enterprise Manager over the network from another PC.
If I create another instance of MSDE on the PC, can I remove / uninstall the first instance?
Is it any big deal if I leave the first instance?
and last but not least...
If I create a new instance will it see the database I created on the first instance?
told you so...
Can anyone recomend some good literature to explain about instances please :D
Hi,
we have about 12 databases in an MSDE server.
We can install the enterprise manager, but cant get the maintenance plans to work. (fields stay empty and setting dont get saved)
I've searched a bit, but couldn't fix it.
No problem, i created a job, with 12 T-SQL commands, and the backups are working perfect.
Now we wanne backup with a history (we take a backup every 4 hours of the day), because now they always get overwritten.
So im trying to change my transactSQL to create dynamic files.
The code i'm using right now :
BACKUP DATABASE PW0001A00 TO DISK = 'C:SQLBACKUPPW0001A00.BAK' WITH INIT, NOUNLOAD, NOSKIP, STATS=10, NOFORMAT
I've tried different things like
BACKUP DATABASE PW0001A00 TO DISK = 'C:SQLBACKUPPW0001A00' + DATEPART(month, GETDATE()) + '.BAK' WITH INIT, NOUNLOAD, NOSKIP, STATS=10, NOFORMAT
but nothing worked.
so basically I wanne be able to create dynamic filenames in the above command.
You got any ideas/solutions.
tnx.
Hi,
I have installed MSDE into my system, in which SQL Server is not installed. I have followed the installation instructions and installed MSDE.
How can i connect to the database? I have the .MDF file of the database (already created in sqlserver) in the Data Folder. How can i connect to the database from Visual Basic 6?